Fwd: lost master password
Sent from Gmail Mobile -- Forwarded message - From: Afa Jamal Date: Fri, Jun 7, 2024 at 8:13 PM Subject: lost master password To: Hello how you can help me with lost super password? thank you -- *Afa J Zada*
Re: Fwd: lost master password
On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote: > how you can help me with lost super password? Are you talking about a PostgreSQL superuser password or about the "master password" that the pgAdmin client uses to encrypt connection information? Yours, Laurenz Albe
Re: lost master password
Hi Do following Stop pg service Open pg_hba.conf Put trust for authentication e.g. "local all all trust" Start the service Log in using psql e.g psql -U postgres Change the password e.g ALTER USER postgres WITH PASSWORD 'new_password'; Regards Kashif Zeeshan Bitnine Global On Mon, Jun 10, 2024 at 2:01 PM Afa Jamal wrote: > > > Sent from Gmail Mobile > > > -- Forwarded message - > From: Afa Jamal > Date: Fri, Jun 7, 2024 at 8:13 PM > Subject: lost master password > To: > > > Hello > how you can help me with lost super password? > > thank you > > > -- > *Afa J Zada* >
Re: Fwd: lost master password
On Mon, 2024-06-10 at 05:06 -0400, Afa Jamal wrote: > On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe wrote: > > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote: > > > how you can help me with lost super password? > > > > Are you talking about a PostgreSQL superuser password or > > about the "master password" that the pgAdmin client uses > > to encrypt connection information? > > superuser https://lmgtfy.click/?q=reset%20postgresql%20superuser%20password Yours, Laurenz Albe
Re: PG16.1 security breach?
On Fri, 2024-06-07 at 07:42 -0700, David G. Johnston wrote: > On Friday, June 7, 2024, Laurenz Albe wrote: > > On Fri, 2024-06-07 at 13:54 +, Zwettler Markus (OIZ) wrote: > > > > Another point to keep in mind is that by default, execute privilege is > > > > granted to > > > > PUBLIC for newly created functions (see Section 5.7 for more > > > > information). > > > > > > Argh. No! What a bad habit! > > > > > > Might be good idea for an enhancement request to create a global > > > parameter to disable this habit. > > > > I don't see the problem, since the default execution mode for functions is > > SECURITY INVOKER. > > > > But you can easily change that: > > > > ALTER DEFAULT PRIVILEGES FOR ROLE function_creator REVOKE EXECUTE ON > > FUNCTION FROM PUBLIC; > > You named function_creator here when in this example the role creating the > new object is postgres. Then use "postgres" rather than "function_creator". An ALTER DEFAULT PRIVILEGES statement always only changes default privileges for objects created by a certain user. > How is it that the default privilege granted to public doesn’t seem to care > who the object creator > is yet when revoking the grant one supposedly can only do so within the scope > of a single role? I don't understand what you wrote. ALTER DEFAULT PRIVILEGES also only applies to objects created by a single role when you grant default privileges. Yours, Laurenz Albe
Re: Fwd: lost master password
superuser Sent from Gmail Mobile On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe wrote: > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote: > > how you can help me with lost super password? > > Are you talking about a PostgreSQL superuser password or > about the "master password" that the pgAdmin client uses > to encrypt connection information? > > Yours, > Laurenz Albe >
Question regarding automatically paused streaming replication
Hi everyone I run a PostgreSQL 14 environment with one primary and two standby instances with streaming replication. I recently had a case where the streaming replication on one of the standby instances paused (seemingly automatically): 2024-06-01 01:11:38.482 CEST [4575476] @ app=LOG: recovery has paused 2024-06-01 01:11:38.482 CEST [4575477] @ app=HINT: Execute pg_wal_replay_resume() to continue. The log messages around this time all look normal and there were no outages. Are there any circumstances under which replication would automatically pause without outside intervention? There was no change of the primary during this time (we do not load balance queries to the standby nodes, they exist soley for HA). I manually resumed using the suggested command and it's been running fine since (and it's been running for about a year until the pause without issue the other day). For reference, the full environment consists of the three PostgreSQL nodes, with EnterpriseDB Enterprise Failover Manager (but as far as I can tell this didn't intervene at the time either), and three PgPool nodes. Many thanks Ian van der Linde
Vacuum backend with backend_xmin?
Hi, This is a VACUUM FREEZE process. -[ RECORD 1 ]--+-- pid| 129471 datid | 16401 datname| feed relid | 1889166 phase | scanning heap heap_blks_total| 1254901 heap_blks_scanned | 1017524 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples| 11184809 num_dead_tuples| 0 backend_xid| backend_xmin | 3267908740 age| 8572 The query is: select v.*, a.backend_xid, a.backend_xmin, age(a.backend_xmin) from pg_stat_progress_vacuum as v join pg_stat_activity as a on a.pid=v.pid Now, my question is why does a vacuum backend have a backend_xmin? I am just curious. Thanks, Torsten
Escaping single quotes with backslash seems not to work
PG 9.6 and PG 14
https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
[quote]
Any other character following a backslash is taken literally. Thus, to
include a backslash character, write two backslashes (\\). Also, a single
quote can be included in an escape string by writing \', in addition to the
normal way of ''.
[/quote]
But it doesn't seem to work. Obviously there's some misconfiguration or ,
but I don't see what I did wrong.
TAP=# insert into foo (name, description) values ('XYZ_Name ', '''XYZ ''');
INSERT 0 1
TAP=# insert into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP'#
TAP'# ');
ERROR: syntax error at or near "XYZ"
LINE 1: ...into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP=# show standard_conforming_strings;
standard_conforming_strings
-
on
(1 row)
TAP=#
TAP=# show backslash_quote;
backslash_quote
-
safe_encoding
(1 row)
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson wrote: > PG 9.6 and PG 14 > > > https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS > > [quote] > Any other character following a backslash is taken literally. Thus, to > include a backslash character, write two backslashes (\\). Also, a single > quote can be included in an escape string by writing \', in addition to > the normal way of ''. > [/quote] > > The link you provided goes to the wrong subsection. The following subsection, which discusses, String Constants With C-Style Escapes, requires that you write the literal as E'abc\'def' Note the E prefix on the literal, which is the thing that enables considering backslash as an escape. David J.
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 7:07 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson > wrote: > >> PG 9.6 and PG 14 >> >> >> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS >> >> [quote] >> Any other character following a backslash is taken literally. Thus, to >> include a backslash character, write two backslashes (\\). Also, a >> single quote can be included in an escape string by writing \', in >> addition to the normal way of ''. >> [/quote] >> >> > The link you provided goes to the wrong subsection. > Ah...you just linked to the section on constants where all the various incarnations are subsections. It would be a nice addition to include some examples in the section pertaining to escape syntax. Make it more clear how it differs from just a non-escaping literal. Maybe put something like \n into the basic literal section showing that it outputs the literal two characters instead of a newline. David J.
libpq v17 PQsocketPoll timeout is not granular enough
Hi. I've noticed [that libpq API in v17 beta1][1], and wanted to use it to replace an existing Boost.ASIO-based async polling of the connection's socket, waiting for notifications. The use case being using PostgreSQL LISTEN/NOTIFY for a simple message queue. The code needs to be cross-platform Windows and Linux. My goal would be to eliminate that Boost.ASIO dependency for that, to use just libpq. PQsocketPoll() being based on time_t, it has only second resolution, AFAIK. Despite the [underlying implementation in fe-misc.c][2] supporting at least milliseconds. My use case is clients posting "requests" to the "queue" (i.e. a PostgreSQL table), which trigger NOTIFY messages, waited on by "servers"; and those servers informing back clients via further notifications (on per-request channels) about the processing status of their requests. In that use case, second-resolution on long-lived servers is OK. But OTOH, for the client side, waiting 1s or more to know whether a server picked up their request is "too long / slow". I'd need millisecond timeouts for that. The doc for PQsocketPoll() mentions a different use case for that API. But I think it would a pity if that unreleased API couldn't be made to accomodate sub-second timeouts and more use-cases, like above. Especially given that libpq v17 isn't out yet. I may come late to the game, but hopefully it is not too late. Thoughts? Thanks, --DD [1]: https://www.postgresql.org/docs/17/libpq-connect.html#LIBPQ-PQSOCKETPOLL [2]: https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-misc.c#L1086
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson > wrote: > >> PG 9.6 and PG 14 >> >> >> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS >> >> [quote] >> Any other character following a backslash is taken literally. Thus, to >> include a backslash character, write two backslashes (\\). Also, a >> single quote can be included in an escape string by writing \', in >> addition to the normal way of ''. >> [/quote] >> >> > The link you provided goes to the wrong subsection. The following > subsection, which discusses, String Constants With C-Style Escapes, > requires that you write the literal as E'abc\'def' > > Note the E prefix on the literal, which is the thing that enables > considering backslash as an escape. > This hasn't changed from 9.6, has it? A Java app that uses backslash escapes broke this morning on fields with single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I don't know why. I'm not a Java programmer, though.
Re: Escaping single quotes with backslash seems not to work
On Monday, June 10, 2024, Ron Johnson wrote: > On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson >> wrote: >> >>> PG 9.6 and PG 14 >>> >>> https://www.postgresql.org/docs/14/sql-syntax-lexical. >>> html#SQL-SYNTAX-CONSTANTS >>> >>> [quote] >>> Any other character following a backslash is taken literally. Thus, to >>> include a backslash character, write two backslashes (\\). Also, a >>> single quote can be included in an escape string by writing \', in >>> addition to the normal way of ''. >>> [/quote] >>> >>> >> The link you provided goes to the wrong subsection. The following >> subsection, which discusses, String Constants With C-Style Escapes, >> requires that you write the literal as E'abc\'def' >> >> Note the E prefix on the literal, which is the thing that enables >> considering backslash as an escape. >> > > This hasn't changed from 9.6, has it? > > A Java app that uses backslash escapes broke this morning on fields with > single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I > don't know why. I'm not a Java programmer, though. > > As the caution on that page says the default for standard conforming strings changed in 9.1. But maybe your 9.6 had the old value configured but when you upgraded to 14 you decided to go with the new default. David J.
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, June 10, 2024, Ron Johnson wrote: > >> On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson >>> wrote: >>> PG 9.6 and PG 14 https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS [quote] Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''. [/quote] >>> The link you provided goes to the wrong subsection. The following >>> subsection, which discusses, String Constants With C-Style Escapes, >>> requires that you write the literal as E'abc\'def' >>> >>> Note the E prefix on the literal, which is the thing that enables >>> considering backslash as an escape. >>> >> >> This hasn't changed from 9.6, has it? >> >> A Java app that uses backslash escapes broke this morning on fields with >> single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I >> don't know why. I'm not a Java programmer, though. >> >> > As the caution on that page says the default for standard conforming > strings changed in 9.1. But maybe your 9.6 had the old value configured but > when you upgraded to 14 you decided to go with the new default. > That was the first thing I checked... It's the same on both the 9.6 and 14 systems:. TAP=# show standard_conforming_strings; standard_conforming_strings - on (1 row) TAP=# TAP=# show backslash_quote; backslash_quote - safe_encoding (1 row)
Re: Escaping single quotes with backslash seems not to work
Ron Johnson writes: > On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: >> As the caution on that page says the default for standard conforming >> strings changed in 9.1. But maybe your 9.6 had the old value configured but >> when you upgraded to 14 you decided to go with the new default. > That was the first thing I checked... It's the same on both the 9.6 and 14 > systems:. Did you check that as the user that runs the Java app (I sure hope it's not the superuser you evidently used here), in the DB the Java app uses? I'm wondering about per-user or per-DB settings of standard_conforming_strings. regards, tom lane
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 11:08 AM Tom Lane wrote: > Ron Johnson writes: > > On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston < > > david.g.johns...@gmail.com> wrote: > >> As the caution on that page says the default for standard conforming > >> strings changed in 9.1. But maybe your 9.6 had the old value configured > but > >> when you upgraded to 14 you decided to go with the new default. > > > That was the first thing I checked... It's the same on both the 9.6 and > 14 > > systems:. > > Did you check that as the user that runs the Java app (I sure hope > it's not the superuser you evidently used here), in the DB the Java > app uses? I'm wondering about per-user or per-DB settings of > standard_conforming_strings. > It's a remote Java app which runs as a non-superuser. I don't know what it's doing. I ran "pg_dumpuser -g" on the old systems, and applied the sql to the corresponding new servers. "set standard_encoding_strings = on" is at the top, and there's no other reference to it.
Re: Escaping single quotes with backslash seems not to work
On 6/10/24 07:52, Ron Johnson wrote: On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: This hasn't changed from 9.6, has it? A Java app that uses backslash escapes broke this morning on fields with single quotes, after the weekend migration from PG 9.6.24 to 14.12, and I don't know why. I'm not a Java programmer, though. Was there a change in the JDBC driver also? -- Adrian Klaver adrian.kla...@aklaver.com
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson wrote: > > "set standard_encoding_strings = on" is at the top, and there's no other > reference to it. > > Well, if they are not using E-strings for escapes then you have the answer why v14 is broken. Does it really matter why v9.6 apparently worked even though it should not have if that setting was also set to on? David J.
Re: Vacuum backend with backend_xmin?
On Mon, 2024-06-10 at 14:06 +0200, Torsten Förtsch wrote: > Now, my question is why does a vacuum backend have a backend_xmin? I am just > curious. I'd say because it sees a certain snapshot of the database, like all other statements. Yours, Laurenz Albe
Re: libpq v17 PQsocketPoll timeout is not granular enough
Dominique Devienne writes: > PQsocketPoll() being based on time_t, it has only second resolution, AFAIK. > Despite the [underlying implementation in fe-misc.c][2] supporting at > least milliseconds. > ... > But I think it would a pity if that unreleased API couldn't be made to > accomodate sub-second timeouts and more use-cases, like above. > Especially given that libpq v17 isn't out yet. I may come late to the > game, but hopefully it is not too late. This is an interesting suggestion, but I think yes it's too late. We're already past beta1 and this'd require some fairly fundamental rethinking, since there's no easy substitute for type time_t that has millisecond resolution. (The callers do want to specify an end time not a timeout interval, since some of them loop around PQsocketPoll and don't want the end time to slip.) I guess conceivably we could use gettimeofday() and struct timeval instead of time() and time_t, but it'd touch a lot of places in libpq and it'd make some of the calculations a lot more complex. Maybe a better idea would be to convert to using our src/include/portability/instr_time.h abstraction? But that would be problematic for outside callers. In any case this doesn't seem like a sane thing to be redesigning post-beta. A few months ago maybe we'd have done it, but ... regards, tom lane
Re: Escaping single quotes with backslash seems not to work
On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson > wrote: > >> >> "set standard_encoding_strings = on" is at the top, and there's no other >> reference to it. >> >> > Well, if they are not using E-strings for escapes then you have the answer > why v14 is broken. Does it really matter why v9.6 apparently worked even > though it should not have if that setting was also set to on? > It matters that *something broke* either between PG 9.6 and 14 *OR* the old JDBC driver and the new JDBC driver, because the client end users are HOPPING MAD. (Don't ask why it wasn't caught in testing; that's beyond my control.)
Re: libpq v17 PQsocketPoll timeout is not granular enough
Bummer… I didn’t presume to suggest an api before, but simply adding an extra int with the milliseconds offset from the time_t is simple, and trivial to plug into the implementation I saw. Callers who don’t care can simply pass zero. while I could pass a computed time_t and ms offset using . No need for fancy types imho. Aren’t betas precisely for the purpose of exposing apis to those like myself to vet them? This is also beta1, I,e, the first one. My €0.02 On Mon, Jun 10, 2024 at 6:18 PM Tom Lane wrote: > Dominique Devienne writes: > > PQsocketPoll() being based on time_t, it has only second resolution, > AFAIK. > > Despite the [underlying implementation in fe-misc.c][2] supporting at > > least milliseconds. > > ... > > But I think it would a pity if that unreleased API couldn't be made to > > accomodate sub-second timeouts and more use-cases, like above. > > Especially given that libpq v17 isn't out yet. I may come late to the > > game, but hopefully it is not too late. > > This is an interesting suggestion, but I think yes it's too late. > We're already past beta1 and this'd require some fairly fundamental > rethinking, since there's no easy substitute for type time_t that has > millisecond resolution. (The callers do want to specify an end time > not a timeout interval, since some of them loop around PQsocketPoll > and don't want the end time to slip.) > > I guess conceivably we could use gettimeofday() and struct timeval > instead of time() and time_t, but it'd touch a lot of places in > libpq and it'd make some of the calculations a lot more complex. > > Maybe a better idea would be to convert to using our > src/include/portability/instr_time.h abstraction? But that > would be problematic for outside callers. > > In any case this doesn't seem like a sane thing to be redesigning > post-beta. A few months ago maybe we'd have done it, but ... > > regards, tom lane >
Multiple tables row insertions from single psql input file
My business tracking database has three main tables: company, location, contact. The company and contact primary keys are sequences. I've been adding new rows using INSERT INTO files separately for each table after manually finding the last PK for the company and contact tables. The location table has the company PK as a FK; the contact table has both company PK and location PK as foreign keys. Now I will use next_val 'PK' to assign the value for each new table row. My question is whether I can create new rows for all three tables in the same sql source file. Since the location and contact tables require sequence numbers from the company and location tables is there a way to specify, e.g., current_val 'tablename PK' for the related tables? Or, do I still need to enter all new companies before their locations and contact? TIA, Rich
Does trigger only accept functions?
Hi, It's version 15.4 of postgres. We have a requirement to have the audit enabled for the delete queries on the base table. And for that we are planning to have one audit table created for each base table and have triggers on each of the base tables to be fired on delete which will insert records into the audit table. But I see the trigger is not accepting the insert query directly, rather it's asking to call a function and to put the business logic inside that function, something as below. So does that mean, to enable audit on the ~50 base table , we will have ~50 functions to be created and also they need to be called from ~50 triggers? or any other better approach exists to handle this? CREATE OR REPLACE FUNCTION log_deletes_source_table() RETURNS TRIGGER AS $$ BEGIN INSERT INTO source_table_delete_history (record_id, delete_timestamp, col1, col2,col3) VALUES (OLD.id, current_timestamp, old.col1,old.col2,old.col3); RETURN OLD; END; $$ LANGUAGE plpgsql; -- Trigger for deletes CREATE TRIGGER before_delete BEFORE DELETE ON source_table FOR EACH ROW EXECUTE FUNCTION log_deletes_source_table (); Regards Veem
Re: Multiple tables row insertions from single psql input file
On Mon, Jun 10, 2024 at 2:50 PM Rich Shepard wrote: > My business tracking database has three main tables: company, location, > contact. The company and contact primary keys are sequences. > > I've been adding new rows using INSERT INTO files separately for each table > after manually finding the last PK for the company and contact tables. The > location table has the company PK as a FK; the contact table has both > company PK and location PK as foreign keys. > > Now I will use next_val 'PK' to assign the value for each new table row. > > My question is whether I can create new rows for all three tables in the > same sql source file. Since the location and contact tables require > sequence > numbers from the company and location tables is there a way to specify, > e.g., current_val 'tablename PK' for the related tables? Or, do I still > need > to enter all new companies before their locations and contact? > With enough clever scripting you can create a .sql file that does almost anything. Most useful to you will be some number of "ALTER TABLE DISABLE TRIGGER ALL;" statements near the beginning of the file, and their "ALTER TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.
Re: Multiple tables row insertions from single psql input file
On Mon, Jun 10, 2024 at 8:50 PM Rich Shepard
wrote:
> My question is whether I can create new rows for all three tables in the
> same sql source file. Since the location and contact tables require
> sequence
> numbers from the company and location tables is there a way to specify,
> e.g., current_val 'tablename PK' for the related tables? Or, do I still
> need
> to enter all new companies before their locations and contact?
>
>
Something along these lines perhaps:
=# create table a( id bigserial primary key, x text );
CREATE TABLE
=# create table b( fk bigint references a(id), y text );
CREATE TABLE
=# with ins_a as (insert into a (x) values ('a row') returning *)
insert into b(fk, y) select ins_a.id, 'yy'||i.i from ins_a cross join
generate_series(1,10) as i(i);
INSERT 0 10
=# table a; table b;
id | x
+---
1 | a row
(1 row)
Time: 0.215 ms
fk | y
+--
1 | yy1
1 | yy2
1 | yy3
1 | yy4
1 | yy5
1 | yy6
1 | yy7
1 | yy8
1 | yy9
1 | yy10
(10 rows)
Re: Multiple tables row insertions from single psql input file
On Mon, 10 Jun 2024, Ron Johnson wrote: With enough clever scripting you can create a .sql file that does almost anything. Ron, My projects don't all use SQL so I'm far from a clever scripter. :-) Most useful to you will be some number of "ALTER TABLE DISABLE TRIGGER ALL;" statements near the beginning of the file, and their "ALTER TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file. Doesn't alter table primarily apply to existing row values for specific columns rather than inserting new rows and their column values? Thanks, Rich
Re: Multiple tables row insertions from single psql input file
On Mon, 10 Jun 2024, Torsten Förtsch wrote:
Something along these lines perhaps:
=# create table a( id bigserial primary key, x text );
CREATE TABLE
=# create table b( fk bigint references a(id), y text );
CREATE TABLE
=# with ins_a as (insert into a (x) values ('a row') returning *)
insert into b(fk, y) select ins_a.id, 'yy'||i.i from ins_a cross join
generate_series(1,10) as i(i);
INSERT 0 10
=# table a; table b;
id | x
+---
1 | a row
(1 row)
Time: 0.215 ms
fk | y
+--
1 | yy1
1 | yy2
1 | yy3
1 | yy4
1 | yy5
1 | yy6
1 | yy7
1 | yy8
1 | yy9
1 | yy10
(10 rows)
Torsten,
You answered my question. The tables are already created and I'll need to
insert new rows table-by-table as I've done before now.
Thanks,
Rich
Re: libpq v17 PQsocketPoll timeout is not granular enough
On 6/10/24 11:43, Dominique Devienne wrote: Bummer… I didn’t presume to suggest an api before, but simply adding an extra int with the milliseconds offset from the time_t is simple, and trivial to plug into the implementation I saw. Callers who don’t care can simply pass zero. while I could pass a computed time_t and ms offset using . No need for fancy types imho. Aren’t betas precisely for https://www.postgresql.org/developer/beta/ "PostgreSQL beta and release candidate releases are pre-release testing versions before the community makes a new release generally available. They are feature-frozen (i.e. no new features are added), and we release these to the public for testing before our final release. PostgreSQL beta and release candidate release are not meant for use in production systems." the purpose of exposing apis to those like myself to vet them? This is also beta1, I,e, the first one. My €0.02 -- Adrian Klaver adrian.kla...@aklaver.com
Re: Does trigger only accept functions?
On 6/10/24 12:17, veem v wrote: Hi, It's version 15.4 of postgres. We have a requirement to have the audit enabled for the delete queries on the base table. And for that we are planning to have one audit table created for each base table and have triggers on each of the base tables to be fired on delete which will insert records into the audit table. But I see the trigger is not accepting the insert query directly, rather it's asking to call a function and to put the business logic inside that function, something as below. So does that mean, to enable audit on the ~50 base table , we will have ~50 functions to be created and also they need to be called from ~50 triggers? or any other better approach exists to handle this? The below tells you what you need: https://www.postgresql.org/docs/15/sql-createtrigger.html That is either a function or a procedure. You could create one function with dynamic SQL and call that from each trigger. Yes there would need to be trigger on each table in that case. As to alternatives: https://www.pgaudit.org/ CREATE OR REPLACE FUNCTION log_deletes_source_table() RETURNS TRIGGER AS $$ BEGIN INSERT INTO source_table_delete_history (record_id, delete_timestamp, col1, col2,col3) VALUES (OLD.id, current_timestamp, old.col1,old.col2,old.col3); RETURN OLD; END; $$ LANGUAGE plpgsql; -- Trigger for deletes CREATE TRIGGER before_delete BEFORE DELETE ON source_table FOR EACH ROW EXECUTE FUNCTION log_deletes_source_table (); Regards Veem -- Adrian Klaver adrian.kla...@aklaver.com
Re: Multiple tables row insertions from single psql input file
On Mon, Jun 10, 2024 at 4:06 PM Rich Shepard wrote: > On Mon, 10 Jun 2024, Ron Johnson wrote: > > > With enough clever scripting you can create a .sql file that does almost > > anything. > > Ron, > > My projects don't all use SQL so I'm far from a clever scripter. :-) > No one is born a scripter, much less a clever scripter. > > Most useful to you will be some number of "ALTER TABLE DISABLE > > TRIGGER ALL;" statements near the beginning of the file, and their "ALTER > > TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file. > > Doesn't alter table primarily apply to existing row values for specific > columns rather than inserting new rows and their column values? > I don't think so. For example, pg_dump has an option to add those DISABLE/ENABLE TRIGGER statements. It makes bulk loading of records much simpler.
Re: Escaping single quotes with backslash seems not to work
On 6/10/24 10:51, Ron Johnson wrote: On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson mailto:ronljohnso...@gmail.com>> wrote: "set standard_encoding_strings = on" is at the top, and there's no other reference to it. Well, if they are not using E-strings for escapes then you have the answer why v14 is broken. Does it really matter why v9.6 apparently worked even though it should not have if that setting was also set to on? It matters that *something broke* either between PG 9.6 and 14 *OR* the old JDBC driver and the new JDBC driver, because the client end users are HOPPING MAD. What was the change in the JDBC driver version? What is the error you are getting currently? (Don't ask why it wasn't caught in testing; that's beyond my control.) -- Adrian Klaver adrian.kla...@aklaver.com
Re: Multiple tables row insertions from single psql input file
> > On Mon, Jun 10, 2024 at 12:43 PM Ron Johnson > wrote: > Most useful to you will be some number of "ALTER TABLE DISABLE > TRIGGER ALL;" statements near the beginning of the file, and their "ALTER > TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file. > > Have you just not heard of deferred constraints or is there some reason besides deferring constraints that you'd want to use alter table in transactional production code? David J.
Re: Multiple tables row insertions from single psql input file
On 2024-06-10 11:50:27 -0700, Rich Shepard wrote:
> My business tracking database has three main tables: company, location,
> contact. The company and contact primary keys are sequences.
>
> I've been adding new rows using INSERT INTO files separately for each table
> after manually finding the last PK for the company and contact tables. The
> location table has the company PK as a FK; the contact table has both
> company PK and location PK as foreign keys.
>
> Now I will use next_val 'PK' to assign the value for each new table row.
>
> My question is whether I can create new rows for all three tables in the
> same sql source file.
Yes, of course.
> Since the location and contact tables require sequence
> numbers from the company and location tables is there a way to specify,
> e.g., current_val 'tablename PK' for the related tables?
Yes. The function is called currval() and you have to specify the
sequence, not the key, though. For example:
-- set up test tables:
create table company(id serial primary key, name text);
create table location(id serial primary key, company int references company,
name text);
create table contact(id serial primary key, company int references company,
location int references location, name text);
-- add some data
insert into company (name) values('ACME, Inc.');
insert into location(company, name) values(currval('company_id_seq'),
'Phoenix');
insert into contact(company, location, name) values(currval('company_id_seq'),
currval('location_id_seq'), 'R. Runner');
insert into location(company, name) values(currval('company_id_seq'), 'Los
Angeles');
insert into contact(company, location, name) values(currval('company_id_seq'),
currval('location_id_seq'), 'H. Warner');
insert into company (name) values('Wayne Enterprises');
insert into location(company, name) values(currval('company_id_seq'), 'Gotham
City');
insert into contact(company, location, name) values(currval('company_id_seq'),
currval('location_id_seq'), 'Alfred P.');
This will result in:
test=> select * from company;
╔╤═══╗
║ id │ name║
╟┼───╢
║ 1 │ ACME, Inc.║
║ 2 │ Wayne Enterprises ║
╚╧═══╝
(2 rows)
test=> select * from location;
╔╤═╤═╗
║ id │ company │name ║
╟┼─┼─╢
║ 1 │ 1 │ Phoenix ║
║ 2 │ 1 │ Los Angeles ║
║ 3 │ 2 │ Gotham City ║
╚╧═╧═╝
(3 rows)
test=> select * from contact;
╔╤═╤══╤═══╗
║ id │ company │ location │ name║
╟┼─┼──┼───╢
║ 1 │ 1 │1 │ R. Runner ║
║ 2 │ 1 │2 │ H. Warner ║
║ 3 │ 2 │3 │ Alfred P. ║
╚╧═╧══╧═══╝
(3 rows)
> Or, do I still need to enter all new companies before their locations
> and contact?
You will of course have to enter each company before its location and
each company and location before its contact. Otherwise you won't have a
value to insert into the foreign key field(s).
There is no need to enter all companies before all locations. Indeed,
currval() can only (as the name implies) return the *current* value of a
sequence, so you can only use it to refer to the last entry you created.
If you create two companies in a row, you've lost the id of the first
one (unless you have somehow saved it - or of course you could get it
back with a select).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Multiple tables row insertions from single psql input file
On Mon, 10 Jun 2024, Peter J. Holzer wrote:
My question is whether I can create new rows for all three tables in the
same sql source file.
Yes, of course.
Since the location and contact tables require sequence
numbers from the company and location tables is there a way to specify,
e.g., current_val 'tablename PK' for the related tables?
You will of course have to enter each company before its location and each
company and location before its contact. Otherwise you won't have a value
to insert into the foreign key field(s).
Peter,
That's what I thought was the case; no way to insert new rows in children
tables when the parent is having new rows at the same time.
So, I started with the parent (companies) table but psql is telling me
there's a syntax error and I don't see it. Testing script:
INSERT into companies (company_nbr,company_name,industry,status) VALUES
nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opportunity'),
nextval('companies_org_nbr_seq'),'Baker','Livestock','Opportunity');
Running:
$ psql -U rshepard -d bustrac -f test-script.sql
psql:test-script.sql:3: ERROR: syntax error at or near "nextval"
LINE 2: nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opp...
^
What have I missed?
TIA,
Rich
Re: Multiple tables row insertions from single psql input file
On Mon, Jun 10, 2024 at 2:58 PM Rich Shepard
wrote:
>
> INSERT into companies (company_nbr,company_name,industry,status) VALUES
> nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opportunity'),
> nextval('companies_org_nbr_seq'),'Baker','Livestock','Opportunity');
>
> Running:
> $ psql -U rshepard -d bustrac -f test-script.sql
> psql:test-script.sql:3: ERROR: syntax error at or near "nextval"
> LINE 2: nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opp...
> ^
> What have I missed?
>
>
Values introduces literal records/rows. Rows are written within
parentheses.
Values (..., ...), (..., ...)
You seem to have the closing parenthesis but not the opening one.
David J.
Re: Multiple tables row insertions from single psql input file [RESOLVED]
On Mon, 10 Jun 2024, David G. Johnston wrote: Values introduces literal records/rows. Rows are written within parentheses. Values (..., ...), (..., ...) You seem to have the closing parenthesis but not the opening one. David, Duh! Of course. For whatever the reason I kept not seeing that. Many thanks, Rich
Gaps in PK sequence numbers
Over the years I've deleted rows from tables leaving gaps in the PK sequence
numbers. I've now learned that using nextval('sequencename') finds those
gaps and tells me that the value after the gap is already assigned.
I found a web page that explains how to find the gaps in a sequence, yet I
want to understand why nextval() doesn't begin with the max(FK)+1 value.
When I tried inserting new rows in the companies table psql told me that PK
value 2310 already existed. Selecting max(PK) returned 2341. When entering
multiple new rows is there a way to ignore gaps?
TIA,
Rich
Re: libpq v17 PQsocketPoll timeout is not granular enough
On Tue, Jun 11, 2024 at 2:36 AM Dominique Devienne wrote: > Hi. I've noticed [that libpq API in v17 beta1][1], and wanted to use > it to replace an existing Boost.ASIO-based async polling of the > connection's socket, waiting for notifications. The use case being > using PostgreSQL LISTEN/NOTIFY for a simple message queue. The code > needs to be cross-platform Windows and Linux. My goal would be to > eliminate that Boost.ASIO dependency for that, to use just libpq. One idea I have wondered about is why you wouldn't just use poll() directly, if you want a facility that works on all known operating systems without extra portability libraries. Windows has WSApoll(), which AFAIK was designed to be Unix-compatible and a drop-in replacement, requiring just a rename but otherwise having the same macros and struct etc. For some period of time, people who had to deal with socket connection events (that includes us) avoided it, with the Curl guys' blog being the most often cited public explanation for why: https://daniel.haxx.se/blog/2012/10/10/wsapoll-is-broken/ However, as far as I know, that was fixed ~4 years ago: https://learn.microsoft.com/en-us/windows/win32/api/winsock2/nf-winsock2-wsapoll "Note As of Windows 10 version 2004, when a TCP socket fails to connect, (POLLHUP \| POLLERR \| POLLWRNORM) is indicated." I wonder if that means that it's now completely usable on all reasonable versions of the OS. I think so? I don't use Windows myself, my interest in this topic is that I have a slow moving background project to figure out how and when to remove all remaining uses of select() from our tree, and this one is on my hit list. > PQsocketPoll() being based on time_t, it has only second resolution, AFAIK. > Despite the [underlying implementation in fe-misc.c][2] supporting at > least milliseconds. Yeah, that is not nice and your complaint is very reasonable, and we should probably do something like what Tom suggested. Hmm, but if what I speculated above is true, I wonder if the extern function is even worth its bits... but I don't know how to determine that completely.
Re: Gaps in PK sequence numbers
On Mon, Jun 10, 2024 at 3:57 PM Rich Shepard wrote: > > I found a web page that explains how to find the gaps in a sequence, yet I > want to understand why nextval() doesn't begin with the max(FK)+1 value. > For efficiency the only thing used to determine the next value of a sequence is the stored value of the last sequence value issued. Where that value may have been used, in a table as a PK or some other purpose, does not enter into it. Using a sequence as a default does indeed become problematic if you don't use it exclusively. If you do use it exclusively usually you just set the last value to be the maximum needed and use it going forward. The numbers from deleted rows simply remain missing in the table. David J.
Re: Gaps in PK sequence numbers
> On Jun 10, 2024, at 15:57, Rich Shepard wrote: > When I tried inserting new rows in the companies table psql told me that PK > value 2310 already existed. Selecting max(PK) returned 2341. When entering > multiple new rows is there a way to ignore gaps? Strictly speaking, the sequence underlying nextval() has no idea what primary keys are or are not in use. It's just a transaction-ignoring counter that increases with each nextval() call. The only reason that you'd get duplicate key errors in this case are: 1. The sequence was reset to a different, lower value. 2. Rows were inserted that didn't use the sequence to select a primary key.
Re: libpq v17 PQsocketPoll timeout is not granular enough
Thomas Munro writes: > On Tue, Jun 11, 2024 at 2:36 AM Dominique Devienne > wrote: >> PQsocketPoll() being based on time_t, it has only second resolution, AFAIK. >> Despite the [underlying implementation in fe-misc.c][2] supporting at >> least milliseconds. > Yeah, that is not nice and your complaint is very reasonable, and we > should probably do something like what Tom suggested. > Hmm, but if what I speculated above is true, I wonder if the extern > function is even worth its bits... but I don't know how to determine > that completely. I think if we're going to change anything at all here, we should define the external API in microseconds not milliseconds. The lesson we need to be taking from this is that system calls come and go, but libpq API is forever ;-). Somebody could conceivably want sub-millisecond wait resolution within the lifespan of libpq. regards, tom lane
Re: Gaps in PK sequence numbers [RESOLVED]
On Mon, 10 Jun 2024, Christophe Pettus wrote: Strictly speaking, the sequence underlying nextval() has no idea what primary keys are or are not in use. It's just a transaction-ignoring counter that increases with each nextval() call. The only reason that you'd get duplicate key errors in this case are: 1. The sequence was reset to a different, lower value. 2. Rows were inserted that didn't use the sequence to select a primary key. Thanks, Christophe. Is there a way to reset the sequence to the maximum number +1? I don't recall seeing this in the postgres docs but will look again. Regards, Rich
Re: Gaps in PK sequence numbers
On Mon, 10 Jun 2024, David G. Johnston wrote: For efficiency the only thing used to determine the next value of a sequence is the stored value of the last sequence value issued. Where that value may have been used, in a table as a PK or some other purpose, does not enter into it. Using a sequence as a default does indeed become problematic if you don't use it exclusively. If you do use it exclusively usually you just set the last value to be the maximum needed and use it going forward. The numbers from deleted rows simply remain missing in the table. David, Thanks for the explanation. I had entered PKs without using the sequence. Regards, Rich
Re: Gaps in PK sequence numbers [RESOLVED]
> On Jun 10, 2024, at 18:10, Rich Shepard wrote:
> Thanks, Christophe. Is there a way to reset the sequence to the maximum
> number +1? I don't recall seeing this in the postgres docs but will look
> again.
The sequence functions are documented here:
https://www.postgresql.org/docs/current/functions-sequence.html
setval is the function you want. You can use a SELECT so you don't have to
copy values around:
select setval('t_pk_seq', (select max(pk) from t));
That form of setval() sets the sequence so that the next value will be one more
than the value you set it to, so you don't have to do max()+1 there.
Re: Gaps in PK sequence numbers [RESOLVED]
On Monday, June 10, 2024, Rich Shepard wrote: > > Is there a way to reset the sequence to the maximum > number +1? I don't recall seeing this in the postgres docs but will look > again. > https://www.postgresql.org/docs/current/functions-sequence.html setval The bigint argument can be computed from a query. David J.
Re: Does trigger only accept functions?
On Mon, 2024-06-10 at 13:20 -0700, Adrian Klaver wrote: > https://www.postgresql.org/docs/15/sql-createtrigger.html > > That is either a function or a procedure. The trigger function must be a function, it cannot be a procedure. The syntax EXECUTE PROCEDURE is just for backward compatibility with the time before PostgreSQL had procedures. Yours, Laurenz Albe
Re: Vacuum backend with backend_xmin?
Hi On Mon, Jun 10, 2024 at 5:07 PM Torsten Förtsch wrote: > Hi, > > This is a VACUUM FREEZE process. > > -[ RECORD 1 ]--+-- > pid| 129471 > datid | 16401 > datname| feed > relid | 1889166 > phase | scanning heap > heap_blks_total| 1254901 > heap_blks_scanned | 1017524 > heap_blks_vacuumed | 0 > index_vacuum_count | 0 > max_dead_tuples| 11184809 > num_dead_tuples| 0 > backend_xid| > backend_xmin | 3267908740 > age| 8572 > > The query is: > > select v.*, a.backend_xid, a.backend_xmin, age(a.backend_xmin) > from pg_stat_progress_vacuum as v join pg_stat_activity as a on a.pid=v.pid > > Now, my question is why does a vacuum backend have a backend_xmin? I am > just curious. > it is the oldest transaction ID whose effects may not be visible to the transaction running in the backend. Since transaction IDs are stored in each row to determine its visibility, the minimum of the "backend_xmin" of all backends determines the cut-off point beyond which all backends will agree on the visibility of tuples. This is for example relevant for VACUUM: it cannot remove any dead tuples that contain a transaction ID that is not older than any backend's Regards Kashif Zeeshan Bitnine Global > > Thanks, > Torsten >
Re: Multiple tables row insertions from single psql input file
On Mon, Jun 10, 2024 at 5:16 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 10, 2024 at 12:43 PM Ron Johnson >> wrote: > > >> Most useful to you will be some number of "ALTER TABLE DISABLE >> TRIGGER ALL;" statements near the beginning of the file, and their "ALTER >> TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file. >> >> > Have you just not heard of deferred constraints or is there some reason > besides deferring constraints that you'd want to use alter table in > transactional production code? > I mentioned bulk loading of data. Occasionally that's useful, even in a prod database.
autoanalyze / autovacuum vs manually executed "vacuum analyze"
Hi everyone, we created an index on a table to improve the performance for a SQL statement. After executing “vacuum analyze mytable”, the index is used. I know that there is also an autovacuum/autoanalyzer configured. I can see that autovacuum and autoanalyzer ran recently. But the index is still not used. I would expect that the index is used after the autovacuum/autoanalyze. Questions: 1. Why would the index not be used after autovacuum/autoanalyze but only after manually running vacuum analyze? 2. Is there any difference between the autovacuum /autoanalyze and the manual exected “vacuum analyze”? Details: 1) Get last analyze and last autoanalyzer dates: select * from pg_stat_all_tables where relname ='mytable' >"last_analyze": "2024-05-07T15:26:01.363796+00:00", >"last_autoanalyze": "2024-06-09T20:52:32.411717+00:00", >"last_autovacuum": "2024-05-20T02:14:34.165689+00:00", >"last_vacuum": "2024-05-07T15:24:42.69+00:00", 2) Explain analyze : no index is used for the SQL statement 3) vacuum analyze mytable -- manually executed 4) Explain analyze : index is now used ✓ Thanks in advance & Best regards, Manuel
