What is referential_action?
Four times, the word "referential_action" is used on this page, but it's never mentioned what the possible referential actions are. Am I missing something? https://www.postgresql.org/docs/14/sql-altertable.html [ CONSTRAINT constraint_name ] [snip] PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } [ CONSTRAINT constraint_name ] [snip] FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
Re: What is referential_action?
> On Apr 8, 2024, at 06:37, Ron Johnson wrote: > > Four times, the word "referential_action" is used on this page, but it's > never mentioned what the possible referential actions are. They're defined in CREATE TABLE: https://www.postgresql.org/docs/14/sql-createtable.html
Re: What is referential_action?
On Monday, April 8, 2024, Ron Johnson wrote: > Four times, the word "referential_action" is used on this page, but it's > never mentioned what the possible referential actions are. > > Am I missing something? > > https://www.postgresql.org/docs/14/sql-altertable.html > Much of the details regarding the things you can alter onto a table are defined in create table reference for the same thing. David J.
Re: What is referential_action?
On Mon, Apr 8, 2024 at 9:41 AM David G. Johnston wrote: > On Monday, April 8, 2024, Ron Johnson wrote: > >> Four times, the word "referential_action" is used on this page, but it's >> never mentioned what the possible referential actions are. >> >> Am I missing something? >> >> https://www.postgresql.org/docs/14/sql-altertable.html >> > > Much of the details regarding the things you can alter onto a table are > defined in create table reference for the same thing. > Thanks, Chris and David.
Storing and comparing columns of cryptographic hashes?
I'm planning to store cryptographic hashes (BLAKE3) in a column of a postgresql table. I'm going to be doing a large number of operations of roughly this form: - Receive a large number of hashes via a web API call. - Check which hashes aren't already in the database. - Send back a bitmap to the user of which hashes they need to send. - Receive data from the user corresponding to those hashes. - Store the data (not in postgresql). - Add the new hashes to the database, along with a tiny value indicating what group of objects they were stored in. A few questions: - Is there a way to tell postgresql "this column contains cryptographic hashes, so you can do hash joins using any subset of the bits, without having to hash them again"? If not, should there be? - Is `bit(256)` the right type to use to store 32-byte hash values with no additional overhead? - What would be the simplest way, given an input array of hashes (which I may have to pass in as an array and use `unnest`), to filter out all the values that already exist, *and* generate a corresponding bitmap in the same order for present/not-present for the entire array (to send back to the user)? Filtering seems easy enough, but generating the bitmap less so. - Does it make more sense to store the values as one row per value, or as one row per group of values? I know that postgresql can store an entire array in one column; could that efficiently support operations like "tell me which of these objects don't exist in any array in this column" or "for all of these objects, tell me all the group-id values for rows containing them"? Thank you, Josh Triplett
PEM install error
While installing PostgreSQL I am getting this error message during the PEM server portion. Do I need the PEM server? If so, what is the solution? Thanks! [cid:b414e51e-598a-44bf-951a-ce754c0fa77d]
Re: Storing and comparing columns of cryptographic hashes?
On Mon, Apr 8, 2024 at 10:08 AM Josh Triplett wrote: > - Is there a way to tell postgresql "this column contains cryptographic > hashes, so you can do hash joins using any subset of the bits, > without having to hash them again"? If not, should there be? > No, and no. (if I understand your question correctly). You could use a functional index, I suppose, but seems premature optimization. > - Is `bit(256)` the right type to use to store 32-byte hash values with no > additional overhead? > No, you would want bytea. I would store the value in a TEXT field, unless you really worried about space savings. The hexadecimal value will be far easier to debug and work with, and you can use a simple b-tree index. - What would be the simplest way, given an input array of hashes (which > I may have to pass in as an array and use `unnest`), to filter out all > the values that already exist, *and* generate a corresponding bitmap > in the same order for present/not-present for the entire array (to > send back to the user)? Filtering seems easy enough, but generating > the bitmap less so. > Something like this: SELECT array_agg(case when t.bhash is null then 1 else 0 end) from unnest(array['blakehash1', 'blakehash2', etc...]) as a(x) left join mytable t on t.bhash = a.x; > - Does it make more sense to store the values as one row per value, or > as one row per group of values? Hard to answer without knowing more, but I'd lean towards simple and one row per value. Your proposal (query db, do external work, update db) also sets of lots of concurrency red flags, so be mindful of that. Cheers, Greg
Re: PEM install error
On 4/8/24 07:24, mark bradley wrote: While installing PostgreSQL I am getting this error message during the PEM server portion. Do I need the PEM server? If so, what is the solution? PEM Server is an EDB product: https://www.enterprisedb.com/docs/pem/latest/managing_pem_server/ You should reach out to their tech support. Thanks! -- Adrian Klaver adrian.kla...@aklaver.com
prepared statement "cu1" already exists (but it does not)
Hello, In a specific case, I get the error prepared statement "cu1" already exists I understand when this can happen, but in fact I do de-allocate prepared statements when I should. I am investigating on this for several hours now, I thought I could share my problem to see if this ring a bell. I am using PostgreSQL 16.2 or 15.1 (I can try older versions if needed) Platform is Debian 11 I am using the libpq C API to execute SQL statements, mixing: * PQprepare * PQexecPrepared * PQexecParams * PQexec When a prepared statement handle is no longer needed, I do execute deallocate cu1 (seems there is no C API to de-allocate a prepared statement, right?) For result sets (SELECT), I do PQprepare / PQexecPrepared of "server cursors" with: declare cu1 cursor for ... When a server cursor is no longer needed, I do PQexec(conn, "close curs-name') The problem occurs when doing a first transaction, where an INSERT fails because of a table UNIQUE constraint. After the rollback, I restart a new TX with begin, several PQexec() of SELECT and UPDATE succeed, but on a subsequent PQprepare/PQexecPrepared using a "declare cu1 cursor for select ...", I the error "cu1 statement already exists"... and I don't see how cu1 can exist. Before doing the rollback, I try to deallocate the prepared statement with deallocate cu1, but this fails with error: current transaction is aborted, commands ignored until end of transaction block I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX? I just want to make sure that doing a deallocate in this context does not confuse PostgreSQL. I have tried to skip the deallocate in case of SQL error, but on the subsequent PQprepare/PQexecPrepared, I still get the error that the cu1 statement already exists. Is it an issue if I use the same name for a prepared statement and the server cursor? I mean: PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... ) PQexecPrepared(pgConn, "cu1", ... ) My understanding is that a prepared stmt and server cursor are 2 distinct objects. Anyway: I tried to use distinct names but that did not help. Note that when the INSERT succeeds, I do not get the error prepared statement "cu1" already exists Any suggestion is welcome! Seb
LwLocks contention (MultiXactOffsetControlLock/multixact_offset) when running logical replication initial snapshot
Hello, We are currently using a shared PostgreSQL cluster (version 11.18) that supports over ten databases. To alleviate the load on this cluster, we've decided to migrate certain databases to dedicated clusters using the native logical replication feature. This approach has been successfully applied to between 50 and 100 databases without issues. However, we have recently encountered an issue related to LWLocks contention. The problem happened during the taking of an initial snapshot of a slightly bigger database, approximately 500GB, with a single table accounting for 300GB. Although the database remained operational, its performance degraded significantly for some services. Threads experienced delays of 20-30 seconds per simple execution when waiting for the “LWLock:MultiXactOffsetControlLock” and “multixact_offset” locks, which also blocked other processes. This issue did not happen immediately but after a few hours running initial snapshot creation required for logical replication. Interestingly, not all databases or queries were impacted. The performance degradation primarily affected specific queries, which I've listed below with anonymized table names for confidentiality: Database "migrated_db": Insert Query: INSERT INTO library_books (author_id, genre_id, book_id, publisher, library_id, section_key, content) VALUES ($1, $2, $3, $4, $5, $6, $7); Select Query: SELECT $2 FROM ONLY "academic_records"."lecture_series" x WHERE "professor_id" = $1 FOR KEY SHARE OF x; Database "other_db": Update Query: UPDATE "vehicle_registry" SET "mileage_count" = mileage_count + $1 WHERE "vehicle_id" = $2 RETURNING "mileage_count"; These queries experienced significant increases in execution time and shared buffer reads per call. The "library_books" table swelled from 500KB to nearly 800MB, showing increased bloat and the oldest row age. Noticeable drop in transaction rate was visible for affected services. Upon discontinuing the replication, the locks were released, and the "library_books" table returned to its original size of 500KB, with performance levels improving correspondingly. Could you please provide insights on how the initial snapshot for logical replication could be causing these LWLocks contention issues? Furthermore, why are only certain queries affected, including some from non-migrated databases? Would initiating the snapshot with pg_dump, reducing or temporarily removing the workload on the affected queries, or making certain parameter adjustments help resolve this issue? Thank you for your assistance and insights. Best regards, Marko
Re: prepared statement "cu1" already exists (but it does not)
Here a first PQtrace() file... assuming it can help. Seb From: Sebastien Flaesch Sent: Monday, April 8, 2024 5:31 PM To: pgsql-general Cc: Sebastien Flaesch Subject: prepared statement "cu1" already exists (but it does not) Hello, In a specific case, I get the error prepared statement "cu1" already exists I understand when this can happen, but in fact I do de-allocate prepared statements when I should. I am investigating on this for several hours now, I thought I could share my problem to see if this ring a bell. I am using PostgreSQL 16.2 or 15.1 (I can try older versions if needed) Platform is Debian 11 I am using the libpq C API to execute SQL statements, mixing: * PQprepare * PQexecPrepared * PQexecParams * PQexec When a prepared statement handle is no longer needed, I do execute deallocate cu1 (seems there is no C API to de-allocate a prepared statement, right?) For result sets (SELECT), I do PQprepare / PQexecPrepared of "server cursors" with: declare cu1 cursor for ... When a server cursor is no longer needed, I do PQexec(conn, "close curs-name') The problem occurs when doing a first transaction, where an INSERT fails because of a table UNIQUE constraint. After the rollback, I restart a new TX with begin, several PQexec() of SELECT and UPDATE succeed, but on a subsequent PQprepare/PQexecPrepared using a "declare cu1 cursor for select ...", I the error "cu1 statement already exists"... and I don't see how cu1 can exist. Before doing the rollback, I try to deallocate the prepared statement with deallocate cu1, but this fails with error: current transaction is aborted, commands ignored until end of transaction block I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX? I just want to make sure that doing a deallocate in this context does not confuse PostgreSQL. I have tried to skip the deallocate in case of SQL error, but on the subsequent PQprepare/PQexecPrepared, I still get the error that the cu1 statement already exists. Is it an issue if I use the same name for a prepared statement and the server cursor? I mean: PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... ) PQexecPrepared(pgConn, "cu1", ... ) My understanding is that a prepared stmt and server cursor are 2 distinct objects. Anyway: I tried to use distinct names but that did not help. Note that when the INSERT succeeds, I do not get the error prepared statement "cu1" already exists Any suggestion is welcome! Seb 2024-04-08 18:23:11.280594 F 33 Query"set intervalstyle='iso_8601'" 2024-04-08 18:23:11.280740 B 8 CommandComplete "SET" 2024-04-08 18:23:11.280743 B 27 ParameterStatus "IntervalStyle" "iso_8601" 2024-04-08 18:23:11.280745 B 5 ReadyForQueryI 2024-04-08 18:23:11.280781 F 67 Query"update pg_settings set setting=1 where name='lock_timeout'" 2024-04-08 18:23:11.281610 B 35 RowDescription 1 "set_config" 0 0 25 65535 -1 0 2024-04-08 18:23:11.281620 B 13 DataRow 1 3 '10s' 2024-04-08 18:23:11.281622 B 13 CommandComplete "UPDATE 0" 2024-04-08 18:23:11.281623 B 5 ReadyForQueryI 2024-04-08 18:23:11.281698 F 10 Query"begin" 2024-04-08 18:23:11.281720 B 10 CommandComplete "BEGIN" 2024-04-08 18:23:11.281722 B 5 ReadyForQueryT 2024-04-08 18:23:11.281854 F 250 Parse"cu0" "declare srv_cu0 cursor for select udsi_table_name, udsi_last_mtime, udsi_temp_mtime, udsi_can_insert, udsi_can_update, udsi_can_delete, udsi_where_part, udsi_position from dbsync_udsinfo where udsi_user_id = $1 order by udsi_position" 1 1043 2024-04-08 18:23:11.281855 F 4 Sync 2024-04-08 18:23:11.282200 B 4 ParseComplete 2024-04-08 18:23:11.282206 B 5 ReadyForQueryT 2024-04-08 18:23:11.282210 F 27 Bind "" "cu0" 1 0 1 4 'mike' 1 0 2024-04-08 18:23:11.282211 F 6 Describe P "" 2024-04-08 18:23:11.282213 F 9 Execute "" 0 2024-04-08 18:23:11.282214 F 4 Sync 2024-04-08 18:23:11.282512 B 4 BindComplete 2024-04-08 18:23:11.282517 B 4 NoData 2024-04-08 18:23:11.282519 B 19 CommandComplete "DECLARE CURSOR" 2024-04-08 18:23:11.282520 B 5 ReadyForQueryT 2024-04-08 18:23:11.282523 F 34 Query"fetch absolute 0 from srv_cu0" 2024-04-08 18:23:11.282600 B 276 RowDescription 8 "udsi_table_name" 25071 2 1043 65535 54 0 "udsi_last_mtime" 25071 8 1114 8 3 0 "udsi_temp_mtime" 25071 9 1114 8 3 0 "udsi_can_insert" 25071 4 1042 65535 5 0 "udsi_can_update" 25071 5 1042 65535 5 0 "udsi_can_delete" 25071 6
Re: prepared statement "cu1" already exists (but it does not)
On Mon, Apr 8, 2024 at 5:31 PM Sebastien Flaesch wrote: > I understand when this can happen, but in fact I do de-allocate prepared > statements when I should. > We've run into similar issues. We're in C++, and with RAII deallocate resources (Prepared Statements, Cursors) in Dtors. But as you saw, when a TX is KO, any Dtor trying to release resources via libpq will fail. So what I do it record those resources (stmt and cursor names basically) on the Connection (in our wrapper), and will release them at the first opportunity, once the TX has rolled back for example. FWIW. OTOH, we tend not to reuse names, generating random ones, since when using our wrappers, the name is an impl details basically. We also tend to prepare outside transactions, so can't answer your question on whether prepared statements within TX are "scoped" or not. --DD
Re: prepared statement "cu1" already exists (but it does not)
Sebastien Flaesch writes: > I understand that the whole TX is aborted with PostgreSQL, and probably the > deallocate is useless since stmt was prepared inside the TX? As you can quickly discover with some manual experimentation, both PREPARE and DEALLOCATE are nontransactional, in the sense that if they succeed then the prepared statement will exist (or not) even if the surrounding transaction block is later rolled back. This is pretty weird, and undocumented I think, in terms of their role as SQL statements. It makes a little more sense if you think about the equivalent wire-protocol-level operations, which are meant to be used by low-level client code that may not be aware of whether there is a transaction block in progress. > Is it an issue if I use the same name for a prepared statement and the server > cursor? I mean: From memory, I think those share the same "portal" namespace. regards, tom lane
Re: prepared statement "cu1" already exists (but it does not)
Thanks all for your help. I have discovered another fact: When surrounding the execution of the failing INSERT with a savepoint + rollback to savepoint, the problem disappears: The DEALLOCATE commands do then succeed. See attached trace... Seb From: Tom Lane Sent: Monday, April 8, 2024 7:36 PM To: Sebastien Flaesch Cc: pgsql-general Subject: Re: prepared statement "cu1" already exists (but it does not) EXTERNAL: Do not click links or open attachments if you do not recognize the sender. Sebastien Flaesch writes: > I understand that the whole TX is aborted with PostgreSQL, and probably the > deallocate is useless since stmt was prepared inside the TX? As you can quickly discover with some manual experimentation, both PREPARE and DEALLOCATE are nontransactional, in the sense that if they succeed then the prepared statement will exist (or not) even if the surrounding transaction block is later rolled back. This is pretty weird, and undocumented I think, in terms of their role as SQL statements. It makes a little more sense if you think about the equivalent wire-protocol-level operations, which are meant to be used by low-level client code that may not be aware of whether there is a transaction block in progress. > Is it an issue if I use the same name for a prepared statement and the server > cursor? I mean: >From memory, I think those share the same "portal" namespace. regards, tom lane 2024-04-08 19:39:22.679893 F 33 Query"set intervalstyle='iso_8601'" 2024-04-08 19:39:22.679989 B 8 CommandComplete "SET" 2024-04-08 19:39:22.679993 B 27 ParameterStatus "IntervalStyle" "iso_8601" 2024-04-08 19:39:22.679994 B 5 ReadyForQueryI 2024-04-08 19:39:22.680029 F 67 Query"update pg_settings set setting=1 where name='lock_timeout'" 2024-04-08 19:39:22.680939 B 35 RowDescription 1 "set_config" 0 0 25 65535 -1 0 2024-04-08 19:39:22.680945 B 13 DataRow 1 3 '10s' 2024-04-08 19:39:22.680947 B 13 CommandComplete "UPDATE 0" 2024-04-08 19:39:22.680948 B 5 ReadyForQueryI 2024-04-08 19:39:22.681035 F 10 Query"begin" 2024-04-08 19:39:22.681150 B 10 CommandComplete "BEGIN" 2024-04-08 19:39:22.681155 B 5 ReadyForQueryT 2024-04-08 19:39:22.681306 F 250 Parse"cu0" "declare srv_cu0 cursor for select udsi_table_name, udsi_last_mtime, udsi_temp_mtime, udsi_can_insert, udsi_can_update, udsi_can_delete, udsi_where_part, udsi_position from dbsync_udsinfo where udsi_user_id = $1 order by udsi_position" 1 1043 2024-04-08 19:39:22.681308 F 4 Sync 2024-04-08 19:39:22.681739 B 4 ParseComplete 2024-04-08 19:39:22.681744 B 5 ReadyForQueryT 2024-04-08 19:39:22.681748 F 27 Bind "" "cu0" 1 0 1 4 'mike' 1 0 2024-04-08 19:39:22.681750 F 6 Describe P "" 2024-04-08 19:39:22.681751 F 9 Execute "" 0 2024-04-08 19:39:22.681752 F 4 Sync 2024-04-08 19:39:22.682027 B 4 BindComplete 2024-04-08 19:39:22.682033 B 4 NoData 2024-04-08 19:39:22.682034 B 19 CommandComplete "DECLARE CURSOR" 2024-04-08 19:39:22.682036 B 5 ReadyForQueryT 2024-04-08 19:39:22.682038 F 34 Query"fetch absolute 0 from srv_cu0" 2024-04-08 19:39:22.682126 B 276 RowDescription 8 "udsi_table_name" 25071 2 1043 65535 54 0 "udsi_last_mtime" 25071 8 1114 8 3 0 "udsi_temp_mtime" 25071 9 1114 8 3 0 "udsi_can_insert" 25071 4 1042 65535 5 0 "udsi_can_update" 25071 5 1042 65535 5 0 "udsi_can_delete" 25071 6 1042 65535 5 0 "udsi_where_part" 25071 7 1043 65535 2004 0 "udsi_position" 25071 3 23 4 -1 0 2024-04-08 19:39:22.682131 B 12 CommandComplete "FETCH 0" 2024-04-08 19:39:22.682132 B 5 ReadyForQueryT 2024-04-08 19:39:22.682175 F 34 Query"fetch forward 50 from srv_cu0" 2024-04-08 19:39:22.682277 B 276 RowDescription 8 "udsi_table_name" 25071 2 1043 65535 54 0 "udsi_last_mtime" 25071 8 1114 8 3 0 "udsi_temp_mtime" 25071 9 1114 8 3 0 "udsi_can_insert" 25071 4 1042 65535 5 0 "udsi_can_update" 25071 5 1042 65535 5 0 "udsi_can_delete" 25071 6 1042 65535 5 0 "udsi_where_part" 25071 7 1043 65535 2004 0 "udsi_position" 25071 3 23 4 -1 0 2024-04-08 19:39:22.682280 B 71 DataRow 8 6 'author' 23 '2024-04-08 12:59:40.765' -1 1 'Y' 1 'Y' 1 'Y' -1 1 '1' 2024-04-08 19:39:22.682282 B 69 DataRow 8 4 'book' 23 '2024-04-08 12:59:40.765' -1 1 'Y' 1 'Y' 1 'Y' -1 1 '2' 2024-04-08 19:39:22.682284 B 12 CommandComplete "FETCH 2" 2024-04-08 19:39:22.682285 B 5 ReadyForQueryT 2024-04-08 19:39:22.682386
