fly proxy postgres data missing

[mozart409]

Hello,

I’m experiencing an issue with accessing data in my production database. Despite establishing a connection through fly proxy (using a 2-week old connection string) and accessing the database via TablePlus, I’m encountering discrepancies. Specifically, when I run a SELECT * FROM “User” query, TablePlus only displays 7 entries, which contradicts the expected larger number of entries I can confirm through my production web app’s admin panel.

Initially, I suspected that I might have mistakenly linked the staging database to the production application, but this doesn’t seem to be the case. I verified by connecting to the staging database through TablePlus and confirmed that it only contains demo data.

At this point, I’m not entirely certain that my production app is correctly connected to the production database. However, I’ve observed spikes in Network I/O and App Concurrency during load tests on my app, which suggests some level of active database interaction.

Thank you

[mayailurus]

@mozart409

SELECT * FROM “User”

Hi, @mozart409, … This does look like a mismatch, of some kind… Could you perhaps try the following quick guess?

fly pg connect -a your-db-name  # production db
\c database_name_from_connection_string
\d+

select count(*) from "User";
select count(*) from  User ;  -- not the same table, in pg (will case-fold down to "user")

[mozart409]

Hi @mayailurus,

yeah, now something even confusing happened.

postgres=# select count(*) from “User”;
count

 2

(1 row)

postgres=# select count(*) from User;
count

 1

(1 row)

postgres=#

I know that there should be at least three users active.

The same happens with customers

postgres=# select count(*) from “Customer”;
count

 7

(1 row)

In the admin dashboard, I can see 12.

What the hell is going on?

[mayailurus]

@mozart409

postgres=# select count(*) from "User";
2

postgres=# select count(*) from  User ;
1

Indeed, this came out an extra degree more complicated than the phenomenon I intended to illustrate…

The short answer is that you queried two different things here—and, moreover(!), that User is a keyword (and hence would best be avoided as a table name entirely).

---

The following two psql commands are a better route to what I was trying to get at originally:

\d ?ser
\d ?ustomer

The ? in these is a wildcard that will match any single character; in particular, it will turn up both "User" and "user" tables, if you do have both simultaneously.

Sorry for the inadvertent additional wrinkle earlier…

[mozart409]

Thank you, I am learning a lot about Postgres and this is very informational, but I still have the problem that I can see only 7 Customers but on my web app I find 12 Customers.

postgres-# \d ?ustomer
                                Table "public.Customer"
  Column   |              Type              | Collation | Nullable |      Default
-----------+--------------------------------+-----------+----------+-------------------
 id        | text                           |           | not null |
 name      | text                           |           | not null |
 url       | text                           |           | not null |
 rating    | double precision               |           | not null |
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 updatedAt | timestamp(3) without time zone |           | not null |
Indexes:
    "Customer_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE ""Category"" CONSTRAINT "Category_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""Image"" CONSTRAINT "Image_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""Keyword"" CONSTRAINT "Keyword_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""Location"" CONSTRAINT "Location_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""Plan"" CONSTRAINT "Plan_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer"(id) ON UPDATE CASCADE ON DELETE CASCADE

postgres=# select count(*) from "Customer";
 count
-------
     7
(1 row)

[mayailurus]

@mozart409

postgres=# select count(*) from "Customer";
 count
-------
     7

Sorry that didn’t pan out… If you select * from "Customer" order by id at this prompt, how does the result compare to the particular names and urls that you see on the web-app side?

If it’s a subset (first 7 of 12), then this might be an uncommitted transaction…

[mozart409]

I ran the following command and this was the result

postgres=# select id from "Customer" order by id;
            id             
---------------------------
 clp9y8a85000a5o534ys595wv
 clp9y8aq9000o5o53k80dz9it
 clp9y8b3k00125o53fccfj58e
 clp9y8bgc001e5o53yg1x8x3i
 clp9y8bun001q5o53of7025d9
 clp9y8c9300225o53qi4tigd1
 clp9y8clg002e5o5321vzqwob
(7 rows)

postgres=# 

And I think I am one step closer to the solution, because in my webapp I do not find any of those ids.

[mozart409]

It seems like i have 2 locks in the table pg_locks. Do you know what this means?

postgres=# SELECT * FROM pg_locks;
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted | fastpath | waitstart 
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------+-----------
 relation   |        5 |    12073 |      |       |            |               |         |       |          | 9/14887269         | 27242 | AccessShareLock | t       | t        | 
 virtualxid |          |          |      |       | 9/14887269 |               |         |       |          | 9/14887269         | 27242 | ExclusiveLock   | t       | t        | 
(2 rows)

[mayailurus]

This is normal, since your own query uses locks, too. (If you select * from pg_class where oid = 12073, it should reveal that the relation field is pg_locks itself.)

It was a reasonable place to check, though, given the previous thinking about transactions.

Another is pg_stat_activity, which will show recent SQL requests that various clients have made:

select state,
       datname as database_name,  -- a la the \c and \l commands.
       application_name,  -- not always populated (unfortunately).
       xact_start as transaction_started,
       query
from   pg_stat_activity
where  length(query) > 0
and    backend_type = 'client backend'

Caveat: Although this may also sometimes show queries from past (no longer active) sessions, knowing what some of those occasionally were would actually be nice in this context (albeit slightly confusing initially, )…

[mozart409]

I can your command and this is the output:

idle	repmgr	repmgr	NULL	SELECT pg_catalog.pg_is_in_recovery()
idle	postgres		NULL	"
			SELECT *,
				extract(epoch from now() - last_archived_time) AS last_archive_age
			FROM pg_stat_archiver
			"
idle	repmgr		NULL	SELECT version();
idle	remix_what2do_4555		NULL	SELECT version();
idle	remix_what2do_4555		NULL	"SELECT COUNT(*) FROM (SELECT ""public"".""User"".""id"" FROM ""public"".""User"" WHERE 1=1 OFFSET $1) AS ""sub"""
idle	postgres	psql	NULL	"select state,
       datname as database_name,  -- a la the \c and \l commands.
       application_name,  -- not always populated (unfortunately).
       xact_start as transaction_started,
       query
from   pg_stat_activity
where  length(query) > 0
and    backend_type = 'client backend'

select state,
       datname as database_name,  -- a la the \c and \l commands.
       application_name,  -- not always populated (unfortunately).
       xact_start as transaction_started,
       query
from   pg_stat_activity
where  length(query) > 0
and    backend_type = 'client backend';"
idle	remix_what2do_4555		NULL	"SELECT COUNT(*) FROM (SELECT ""public"".""User"".""id"" FROM ""public"".""User"" WHERE 1=1 OFFSET $1) AS ""sub"""
idle	prisma_migrate_shadow_db_32ba9e09-be00-4101-bacc-bacd284167aa		NULL	SELECT version();
active	postgres	TablePlus	2023-12-06 20:57:55.633232+00	"SELECT
	state,
	datname AS database_name, -- a la the \c and \l commands.
	application_name, -- not always populated (unfortunately).
	xact_start AS transaction_started,
	query
FROM
	pg_stat_activity
WHERE
	length(query) > 0
	AND backend_type = 'client backend' LIMIT 100;"

TLDR:
Only my TablePlus Connection has a transaction running.

[mayailurus]

Thanks for giving it a try…

What happens if you do the following in psql?

\l
\c remix_what2do_4555
\dt
select * from "User"

(There are multiple references to this second database (remix_what2do_4555) in your query result, although it’s unclear how old they are.)

[mozart409]

Of course and I am very grateful that you are helping me

psql (15.3 (Debian 15.3-1.pgdg120+1))
Type "help" for help.

postgres=# \l
                                                                          List of databases
                             Name                              |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
---------------------------------------------------------------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres                                                      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 prisma_migrate_shadow_db_32ba9e09-be00-4101-bacc-bacd284167aa | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 remix_what2do_4555                                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 repmgr                                                        | repmgr   | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 template0                                                     | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
                                                               |          |          |            |            |            |                 | postgres=CTc/postgres
 template1                                                     | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
                                                               |          |          |            |            |            |                 | postgres=CTc/postgres
(6 rows)

postgres=# \c remix_what2do_4555
You are now connected to database "remix_what2do_4555" as user "postgres".
remix_what2do_4555=# \dt
                    List of relations
 Schema |        Name        | Type  |       Owner
--------+--------------------+-------+--------------------
 public | Category           | table | remix_what2do_4555
 public | Chat               | table | remix_what2do_4555
 public | Customer           | table | remix_what2do_4555
 public | Image              | table | remix_what2do_4555
 public | Keyword            | table | remix_what2do_4555
 public | Location           | table | remix_what2do_4555
 public | Note               | table | remix_what2do_4555
 public | Password           | table | remix_what2do_4555
 public | Plan               | table | remix_what2do_4555
 public | User               | table | remix_what2do_4555
 public | _prisma_migrations | table | remix_what2do_4555
(11 rows)

remix_what2do_4555=# select * from "User"

I do not understand why there are no users in the “User” table.

[mayailurus]

@mozart409

I do not understand why there are no users in the “User” table.

That is indeed pretty unexpected…

(It did provide the usual explicit 0 rows message at the end?)

Do you recall how you came to have tables across both databases? The DATABASE_URL installed by flyctl in secrets would typically refer to remix_what2do_4555—and your migrations would hence usually run only there.

Maybe it would be worth doing a broader comparison between all pairs of tables that appear in both the \c postgres and the \c remix_what2do_4555 contexts:

• Customer
• Image
• Location
• …

[mozart409]

@mozart409

select * from "User";

Now I logged back in via

fly pg connect

and I found my missing users.

For contenxt here are the commands

\c postgres;
select * from "User";
(2 rows)

\c remix_what2do_4555
select * from "User";
(3 rows)

So I was in the wrong table? That was is that easy, I am still confused? Can you maybe explain what was happening, so I can learn?

Thank you

[mayailurus]

@mozart409

I found my missing users.

Glad to hear it!

@mozart409

So I was in the wrong table? That was is that easy, I am still confused?

Don’t feel bad. This aspect of Postgres has been confusing people for decades…

Basically, there are “databases within the database”.

Officially, the latter is called a “database cluster”, but almost no one uses that terminology in everyday speech.

Anyway, your Postgres server has multiple, isolated sets of tables inside: these are the “databases” listed by the \l command. The remix_what2do_4555 database and the postgres database both contain tables named User in your case, but this is unusual.

The \c command was switching from one set of tables to the other.

\c postgres
select * from "User";

The above queried the User table in the postgres database.

\c remix_what2do_4555
select * from "User";

Whereas this one queried a completely different table—one that happens to also be named User—in the remix_what2do_4555 database.

This feature honestly is useful sometimes (e.g., to allow multiple apps to share a single Postgres machine without needing to rename their _prisma_migrations tables, etc.), although of course it caused confusion here.

I’d suggest SSHing into all of your remix-what2do-4555 instances to make sure that all of their DATABASE_URLs (or other equivalent connection configuration) do refer to the remix_what2do_4555 database and not to the other one:

$ fly m start     -a remix-what2do-4555     # start all
$ fly ssh console -a remix-what2do-4555 -s  # select each machine, in turn
# echo "$DATABASE_URL"  # not the only way of doing things, but the most common

The general form of the URL is…

postgres://user:password@host:port/database_name?options

I.e., every web-app machine should have /remix_what2do_4555 in the /database_name slot.

Likewise, when you connect manually with TablePlus or with psql (fly pg connect), then you will nearly always want to choose the remix_what2do_4555 database (in the \c sense). Otherwise, you end up looking at something completely different from what your web-app manipulates.

Hope this helps!

[mozart409]

Thank you very much. I will ssh into every machine and check the env vars. Have happy holidays and a happy new year.