Re: Check psql parameter is passed, if not ask for it
to quote the documentation:
> If an unquoted colon (:) followed by a psql variable name appears within
an argument, it is replaced by the variable's value, as described in SQL
Interpolation below.
> The forms :'variable_name' and :"variable_name" described there work as
well. The :{?variable_name} syntax allows testing whether a variable is
defined. It is
> substituted by TRUE or FALSE. Escaping the colon with a backslash
protects it from substitution.
See https://www.postgresql.org/docs/16/app-psql.html
On Mon, Aug 5, 2024 at 8:46 PM Wiwwo Staff wrote:
> Hi!
> I want to check if I pass the parameter "param1" to a sql script
> "delme.sql" like
> ```
> \if ':param1'
> \echo :param1;
> \else
> \prompt 'Param1? ' :param1
> \endif
> select :'param1';
> ```
> if parameter `param1` is not passed, the .sql should ask for it.
>
> If I run something like
> `psql -f delme.sql -v param1=1`
> it works, but if i pass
> `psql -f delme.sql -v param1=meh`
> i receive
> `psql:delme.sql:1: error: unrecognized value ":param1" for "\if
> expression": Boolean expected`
>
> How can I implement that?
> TIA, Wiwwo
>
Re: Logical replication slots on slaves/replicas?
A COMMIT record in the WAL has an LSN. I don't know much about debezuim but wal2json can give you this LSN. Then there is this function, pg_last_wal_replay_lsn(). I think you could run it on the replica to see if you are after the point of commit. If you are, you should be able to see the changes made by the transaction. BTW, I am sure I can construct a situation where you see your transaction in the logical wal stream before it becomes visible on the master. In particular, this is possible with a sync replication setup. So, I would not trust to always be able to see the transaction on the master as soon as it is reported committed in the wal stream. All the best, Torsten On Thu, Aug 1, 2024 at 10:42 AM Piotr Andreassen Blasiak < pi...@attendium.com> wrote: > Hi, > > I know that currently logical replication slots are available only for > primary servers. Is there any plan to add this feature to read slaves as > well? My problem is this: > > I want to use debezium to stream changes from postgresql. But, if I stream > changes from the master I can not query my read slaves for related data to > these changes - I need to always query the master which is not scalable. So > either I need a way to be able to know when the change has been propagated > to my read replica so that I can reliably query it, or I am hoping I can > simply read all the changes from the read replica which will mean it is > already up to date when I query it. > > Piotr Andreassen Blasiak > > > > >
Re: Monitoring DB size
Slightly different approach than you might expect. For larger DBs you'd likely want to exclude base and instead use pg_database_size() in addition. postgres(2454884) =# create temp table xx(dir text, sz bigint); CREATE TABLE Time: 2.587 ms postgres(2454884) =# copy xx(sz, dir) from program 'du -s *'; COPY 21 Time: 3.793 ms postgres(2454884) =# select * from xx; dir | sz --+--- base | 26280 global | 568 pg_commit_ts |12 pg_dynshmem | 4 pg_logical |16 pg_multixact |28 pg_notify| 4 pg_replslot | 4 pg_serial| 4 pg_snapshots | 4 pg_stat | 4 pg_stat_tmp | 4 pg_subtrans |12 pg_tblspc| 4 pg_twophase | 4 PG_VERSION | 4 pg_wal | 16392 pg_xact |12 postgresql.auto.conf | 4 postmaster.opts | 4 postmaster.pid | 4 (21 rows) Time: 0.282 ms On Mon, Jul 15, 2024 at 4:42 PM Shenavai, Manuel wrote: > Hi everyone, > > > > we currently capture the db size (pg_database_size) which gives the “Disk > space used by the database with the specified name”. Is it possible to > further split this data how much space is occupied by live tuples, dead > tuples and free space? > > > > We would like to have something like: > > DB Size: 200 MB, live tuple 100MB, Dead Tuple: 20 MB, free space 80MB > > > > Is this possible? > > > > Best regards, > > Manuel >
Re: Accommodating alternative column values
Check this out
https://www.postgresql.org/docs/16/arrays.html#ARRAYS-INPUT
You can use
('{' || email || '}')::varchar(64)[]
or the syntax I suggested earlier.
On Wed, Jul 3, 2024 at 4:13 PM Rich Shepard
wrote:
> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
> > Yeah, the simply cast suggested will not work. You’d have to apply an
> > expression that turns the current contents into an array. The current
> > contents are not likely to be an array literal.
>
> David,
>
> No, it's not now an array.
>
> I thought that this expression would work, but it doesn't:
> bustrac=# alter table people alter column email set data type
> varchar(64)[] using email::varchar(64)[];
> RROR: malformed array literal: "fr...@dmipx.com"
> DETAIL: Array value must start with "{" or dimension information.
>
> If I correctly understand the error detail I'd need to change the contents
> of that column for all 1280 rows to enclose the contents in curly braces
> before I can convert the datatype to an array. Is that correct?
>
> Rich
>
>
>
Re: Accommodating alternative column values
The USING phrase basically answers the question how do I convert an existing value of the old type to the new type. On Wed, Jul 3, 2024 at 4:03 PM Rich Shepard wrote: > On Wed, 3 Jul 2024, Rich Shepard wrote: > > > What I've tried: > > bustrac=# alter table people alter column email set data type > varchar(64) []; > > ERROR: column "email" cannot be cast automatically to type character > > varying[] > > HINT: You might need to specify "USING email::character varying(64)[]". > > What I forgot to mention is that the current datatype is varchar(64) and I > want to make it an array. > > Rich > > >
Re: Accommodating alternative column values
You could try ALTER TABLE ... SET TYPE TEXT[] USING ARRAY[email]::TEXT[] something along these lines. On Wed, Jul 3, 2024 at 3:58 PM Rich Shepard wrote: > On Tue, 2 Jul 2024, Christophe Pettus wrote: > > > To be clear, I wasn't suggesting stuffing them all into a text column > with > > a delimiter, but storing them in a text *array* field, each email address > > one component of the array. > > Christophe, > > I'm not using the proper syntax and the postgres alter table doc has no > example in the alter column choices. > > What I've tried: > bustrac=# alter table people alter column email set data type varchar(64) > []; > ERROR: column "email" cannot be cast automatically to type character > varying[] > HINT: You might need to specify "USING email::character varying(64)[]". > > How do I incorporate the "USING email::..." string? > > TIA, > > Rich > > >
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)
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
Re: Long running query causing XID limit breach
On Sun, May 26, 2024 at 8:46 PM sud wrote: > Would you agree that we should have two standby, one with default > max_standby_streaming_delay (say 10 sec ) which will be mainly used as high > availability and thus will be having minimal lag. and another standby with > max_standby_streaming_delay as "-1" i.e. it will wait indefinitely for the > SELECT queries to finish without caring about the lag, which will be > utilized for the long running SELECT queries. > > And keep the hot_standby_feedback as ON for the first standby which is > used as HA/high availability. And keep the hot_standby_feedback as OFF for > the second standby which is utilized for long running SELECT queries, so > that primary won't be waiting for the response/feedback from this standby > to vacuum its old transactions and that will keep the transaction id wrap > around issue from not happening because of the Read/Select queries on any > of the standby. > Sure. That could work. Perhaps also set statement_timeout on the first replica, just in case.
Re: Long running query causing XID limit breach
On Sun, May 26, 2024 at 11:16 AM sud wrote: > On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch > wrote: > >> On Sat, May 25, 2024 at 11:00 PM sud wrote: >> >>> >>> But i have one question here , does max_standby_streaming_delay = 14 , >>> means the queries on the standby will get cancelled after 14 seconds? >>> >> >> No, your query gets cancelled when it stalls replication for >14 sec. If >> your master is idle and does not send any WAL and the replica has >> caught up, the query can take as long as it wants. >> > > Thank you so much. > For example , in below scenario, > if i have insert query going on on primary instance on table 25th may > partition of TABLE1, and at same time we are selecting data from 24th May > partition , then with "max_standby_streaming_delay = 14" setup , it just > allows the select query to run for any duration without any restriction > even if the WAL gets applied on the standby regularly. Also INSERT query in > primary won't make the standby SELECT queries to cancel as because the WAL > record of INSERT queries on the primary instance is not conflicting to the > exact rows those were being read by the standby. Is my understanding > correct here? > > However, if i have Update/Delete query going on on primary instance on > table 25th may partition of TABLE1 and on the exact same set of rows which > were being read by the standby instance by the SELECT query, then the > application of such WAL record to standby can max wait for 14 seconds and > thus those select query are prone to be cancelled after 14 seconds. Is this > understanding correct? > > If the above is true then it doesn't look good, as because in an OLTP > system there will be a lot of DMLS happening on the writer instances and > there may be many queries running on the reader/standby instances which are > meant to run for hours. And if we say making those SELECT queries run for > hours means compromising an hour of "high availability"/RPO or a lag of an > hour between primary and standby , that doesn't look good. Please > correct me if I am missing something here. > Each query on the replica has a backend_xmin. You can see that in pg_stat_activity. From that backend's perspective, tuples marked as deleted by any transaction greater or equal to backend_xmin are still needed. This does not depend on the table. Now, vacuum writes to the WAL up to which point it has vacuumed on the master. In pg_waldump this looks like so: PRUNE snapshotConflictHorizon: 774, nredirected: 0, ndead: 5, nunused: 0, redirected: [], dead: [2, 4, 6, 8, 10], unused: [], blkref #0: rel 1663/5/16430 blk 0 That snapshotConflictHorizon is also a transaction id. If the backend_xmin of all backends running transactions in the same database (the 5 in 16 63/5/16430) -as the vacuum WAL record is greater than vacuum's snapshotConflictHorizon, then there is no conflict. If any of the backend_xmin's is less, then there is a conflict. This type of conflict is determined by just 2 numbers, the conflict horizon sent by the master in the WAL, and the minimum of all backend_xmins. For your case this means a long running transaction querying table t1 might have a backend_xmin of 223. On the master update and delete operations happen on table T2. Since all the transactions on the master are fast, when vacuum hits T2, the minimum of all backend_xmins on the master might already be 425. So, garbage left over by all transactions up to 424 can be cleaned up. Now that cleanup record reaches the replica. It compares 223>425 which is false. So, there is a conflict. Now the replica can wait until its own horizon reaches 425 or it can kill all backends with a lower backend_xmin. As I understand, hot_standby_feedback does not work for you. Not sure if you can run the query on the master? That would resolve the issues but might generate the same bloat on the master as hot_standby_feedback. Another option I can see is to run long running queries on a dedicated replica with max_standby_streaming_delay set to infinity or something large enough. If you go that way, you could also fetch the WAL from your WAL archive instead of replicating from the master. That way the replica has absolutely no chance to affect the master. Good Luck! Torsten
Re: Long running query causing XID limit breach
On Sat, May 25, 2024 at 11:00 PM sud wrote: > > But i have one question here , does max_standby_streaming_delay = 14 , > means the queries on the standby will get cancelled after 14 seconds? > No, your query gets cancelled when it stalls replication for >14 sec. If your master is idle and does not send any WAL and the replica has caught up, the query can take as long as it wants.
Re: Backup failure Postgres
Look, you have to compromise somewhere. Let me explain the problem. PG uses MVCC. That means if you update or delete rows, rows are not actually modified or added back to free space. They are just marked for later removal. That actual removal is VACUUM's task. The reason for doing so is that a concurrent transaction might still need to see the modified or deleted row. Now vacuum comes along and wants to actually add things back to free space. On the master that works fine because the master knows all concurrent transactions and what they might still need. So, vacuum will simply skip those rows. However, that knowledge does not extend to the replica. The master does not know which transactions are running on the replica. So a vacuum operation on the master might remove something that's still needed on the replica. Now, that modification made by vacuum also needs to be replayed on the replica. The way that works is by adding all modifications including insert or vacuum or any other change in sequential order to a log (write-ahead-log or WAL). This log is then simply shipped to the replica and replayed. It's not difficult to understand that these changes must be replayed in the same sequential order. Otherwise you get chaos. Now imagine a vacuum operation at the replica which removes stuff that is still needed by a transaction running on the replica like your COPY. Now the replica has 2 choices: - abort the transaction and prefer replaying WAL - pause replaying WAL and wait for the long running transaction The 1st case is obviously bad for the transaction. The 2nd choice is bad for everybody else because WAL can be replayed only in the same order as it is generated. So, nothing that happened after that vacuum can be replayed which leads to stale data on the replica. One way to mitigate this is hot_standby_feedback. That way the replica tells the master from time to time which old rows it still needs to see. The drawback of this is that your tables on the master might accumulate garbage that would normally be removed by vacuum earlier. That can affect query performance. Then you have the option to pause WAL replay one or the other way. max_standby_streaming_delay, disconnecting from the master or explicitly pausing replay, all fall in that category. The last option I know of would be to use logical replication. That comes with other problems. DDL becomes a bit finicky. Initial setup can be tricky. The process applying the changes can become a bottleneck. If you are really time-critical and you just want the COPY job to be done and neither lag nor bloat are acceptable, then maybe you create another streaming replica, disconnect it from the master, run your COPY job and destroy the replica. If 3TB is the database size, then that does not look unsurmountable. Of course, you need the resources. In my environment I'd estimate 3-4 hours. If you want a simple solution, then try hot_standby_feedback. On Thu, May 23, 2024 at 12:46 PM Jethish Jethish wrote: > Hi Torsten, > > I have tried by increasing the max_standby_streaming_delay but I'm facing > lag issues on the replica server. > > When i increase the max_standby_streaming_delay even if a query runs for 2 > minutes I'm facing lag issues for 2 minutes. > > Please suggest here. > Data size is 3TB > > On Thu, May 23, 2024, 3:53 PM Torsten Förtsch > wrote: > >> As the error message says, your query was aborted due to it conflicting >> with recovery. There are many ways to deal with that. You could enable >> hot_standby_feedback on the replica. You could disconnect the replica from >> the master for the time the COPY takes (reset primary_conninfo). You could >> increase max_standby_streaming_delay. Perhaps you could also wrap the COPY >> operation in pg_wal_replay_pause() / pg_wal_replay_resume(). >> >> On Thu, May 23, 2024 at 11:59 AM Jethish Jethish >> wrote: >> >>> I'm frequently facing the below error while performing backup. Someone >>> please tell how solve this issues. >>> >>> >>> Failed : pg_dump: error: Dumping the contents of table "botsession" >>> failed: PQgetResult() failed. pg_dump: error: Error message from server: >>> ERROR: canceling statement due to conflict with recovery DETAIL: User query >>> might have needed to see row versions that must be removed. pg_dump: error: >>> The command was: COPY public.botsession (id, userid, data, iscompressed) TO >>> stdout; >>> >>
Re: Backup failure Postgres
As the error message says, your query was aborted due to it conflicting with recovery. There are many ways to deal with that. You could enable hot_standby_feedback on the replica. You could disconnect the replica from the master for the time the COPY takes (reset primary_conninfo). You could increase max_standby_streaming_delay. Perhaps you could also wrap the COPY operation in pg_wal_replay_pause() / pg_wal_replay_resume(). On Thu, May 23, 2024 at 11:59 AM Jethish Jethish wrote: > I'm frequently facing the below error while performing backup. Someone > please tell how solve this issues. > > > Failed : pg_dump: error: Dumping the contents of table "botsession" > failed: PQgetResult() failed. pg_dump: error: Error message from server: > ERROR: canceling statement due to conflict with recovery DETAIL: User query > might have needed to see row versions that must be removed. pg_dump: error: > The command was: COPY public.botsession (id, userid, data, iscompressed) TO > stdout; >
Re: Unexpected data when subscribing to logical replication slot
Sorry, to correct myself. The pg_xact bit is written with the next checkpoint. But the COMMIT record in the WAL is there. On Thu, May 9, 2024 at 5:14 PM Torsten Förtsch wrote: > I would not find this behavior surprising in particular if you have a > synchronous replica. According to the documentation of synchronous_commit: > >The local behavior of all non-off modes is to wait for local flush of > WAL to disk. > > This is when the logical decoder sees the item. But that does not mean the > change is visible to other transactions in the MVCC sense. So, if wal2json > and the rest of your stuff is fast enough, then the enrichment query may > very well read old data. > > A transaction being committed means all the WAL has been written (and > usually synced) to disk including the bit in the pg_xact directory. > > On Thu, May 9, 2024 at 9:32 AM Daniel McKenzie < > daniel.mcken...@curvedental.com> wrote: > >> Asynchronous commit introduces the risk of data loss. There is a short >>> time window between the report of transaction completion to the client >>> and the time that the transaction is truly committed. >> >> >> The documentation speaks about synchronous_commit changing how >> transactions change behaviour for the client. So in this case, my psql >> terminal is the client, and I would expect a faster commit (from its >> perspective) and then a period of risk (as a process usually done as part >> of the commit is now being done in the background) but it's not clear how >> that affects a replication slot subscriber. >> >> What we're struggling to understand is: why are we seeing any updates in >> the replication slot before they have been "truly committed"? >> >> There appears to be a state of limbo between updating data and that data >> being available to query (and our subscriber is picking up changes in this >> period of time) but I can't pin down any documentation which describes it. >> >> We've had this running in live now for years without a hiccup so we are >> surprised to learn that we have this massive race condition and it just so >> happens that the hardware is fast enough to process the transaction before >> the .NET application can react to replication slot changes. >> >> Daniel McKenzie >> Software Developer >> >> Office: +1 403.910.5927 x 251 >> Mobile: +44 7712 159045 >> Website: www.curvedental.com >> >> *Curve Dental Confidentiality Notice* >> This message is intended exclusively for the individual or entity to >> which it is addressed. This communication may contain information that >> is proprietary, privileged, confidential, or otherwise legally exempt from >> disclosure. If you are not the named addressee, you are not authorized >> to read, print, retain, copy, or disseminate this message or any part of >> it. If you have received this message in error, please notify the sender >> immediately by replying to this e-mail and delete all copies of this >> message. >> >> >> On Wed, May 8, 2024 at 5:28 PM Adrian Klaver >> wrote: >> >>> On 5/8/24 08:24, Daniel McKenzie wrote: >>> > It's running both (in docker containers) and also quite a few more >>> > docker containers running various .NET applications. >>> >>> I think what you found is that the r7a.medium instance is not capable >>> enough to do all that it is asked without introducing lag under load. >>> Answering the questions posed by Tomas Vondra would help get to the >>> actual cause of the lag. >>> >>> In meantime my suspicion is this part: >>> >>> "For example, when I use a psql terminal to update a user's last name >>> from "Jones" to "Smith" then I would expect the enrichment query to find >>> "Smith" but it will sometimes still find "Jones". It finds the old data >>> perhaps 1 in 50 times." >>> >>> If this is being run against the Postgres server my guess is that >>> synchronous_commit=on is causing the commit on the server to wait for >>> the WAL records to be flushed to disk and this is not happening in a >>> timely manner in the '... 1 in 50 times' you mention. In that case you >>> see the old values not the new committed values. This seems to be >>> confirmed when you set synchronous_commit=off and don't see old values. >>> For completeness per: >>> >>> https://www.postgresql.org/docs/current/wal-async-commit.html >>> >>> "However, for short transactions this delay is a major c
Re: Unexpected data when subscribing to logical replication slot
I would not find this behavior surprising in particular if you have a synchronous replica. According to the documentation of synchronous_commit: The local behavior of all non-off modes is to wait for local flush of WAL to disk. This is when the logical decoder sees the item. But that does not mean the change is visible to other transactions in the MVCC sense. So, if wal2json and the rest of your stuff is fast enough, then the enrichment query may very well read old data. A transaction being committed means all the WAL has been written (and usually synced) to disk including the bit in the pg_xact directory. On Thu, May 9, 2024 at 9:32 AM Daniel McKenzie < daniel.mcken...@curvedental.com> wrote: > Asynchronous commit introduces the risk of data loss. There is a short >> time window between the report of transaction completion to the client >> and the time that the transaction is truly committed. > > > The documentation speaks about synchronous_commit changing how > transactions change behaviour for the client. So in this case, my psql > terminal is the client, and I would expect a faster commit (from its > perspective) and then a period of risk (as a process usually done as part > of the commit is now being done in the background) but it's not clear how > that affects a replication slot subscriber. > > What we're struggling to understand is: why are we seeing any updates in > the replication slot before they have been "truly committed"? > > There appears to be a state of limbo between updating data and that data > being available to query (and our subscriber is picking up changes in this > period of time) but I can't pin down any documentation which describes it. > > We've had this running in live now for years without a hiccup so we are > surprised to learn that we have this massive race condition and it just so > happens that the hardware is fast enough to process the transaction before > the .NET application can react to replication slot changes. > > Daniel McKenzie > Software Developer > > Office: +1 403.910.5927 x 251 > Mobile: +44 7712 159045 > Website: www.curvedental.com > > *Curve Dental Confidentiality Notice* > This message is intended exclusively for the individual or entity to which > it is addressed. This communication may contain information that is > proprietary, privileged, confidential, or otherwise legally exempt from > disclosure. If you are not the named addressee, you are not authorized to > read, print, retain, copy, or disseminate this message or any part of it. > If you have received this message in error, please notify the sender > immediately by replying to this e-mail and delete all copies of this > message. > > > On Wed, May 8, 2024 at 5:28 PM Adrian Klaver > wrote: > >> On 5/8/24 08:24, Daniel McKenzie wrote: >> > It's running both (in docker containers) and also quite a few more >> > docker containers running various .NET applications. >> >> I think what you found is that the r7a.medium instance is not capable >> enough to do all that it is asked without introducing lag under load. >> Answering the questions posed by Tomas Vondra would help get to the >> actual cause of the lag. >> >> In meantime my suspicion is this part: >> >> "For example, when I use a psql terminal to update a user's last name >> from "Jones" to "Smith" then I would expect the enrichment query to find >> "Smith" but it will sometimes still find "Jones". It finds the old data >> perhaps 1 in 50 times." >> >> If this is being run against the Postgres server my guess is that >> synchronous_commit=on is causing the commit on the server to wait for >> the WAL records to be flushed to disk and this is not happening in a >> timely manner in the '... 1 in 50 times' you mention. In that case you >> see the old values not the new committed values. This seems to be >> confirmed when you set synchronous_commit=off and don't see old values. >> For completeness per: >> >> https://www.postgresql.org/docs/current/wal-async-commit.html >> >> "However, for short transactions this delay is a major component of the >> total transaction time. Selecting asynchronous commit mode means that >> the server returns success as soon as the transaction is logically >> completed, before the WAL records it generated have actually made their >> way to disk. This can provide a significant boost in throughput for >> small transactions. >> >> Asynchronous commit introduces the risk of data loss. There is a short >> time window between the report of transaction completion to the client >> and the time that the transaction is truly committed (that is, it is >> guaranteed not to be lost if the server crashes). ... >> " >> >> > >> > Daniel McKenzie >> > Software Developer >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >>
Re: pg_notify contention
As part of the commit operation, Postgres inserts the notification into a
queue. Naturally, that insert is guarded by a lock and that lock is
released only at the very end of the commit operation. This effect gets
much worse if you also configure synchronous replication because commit
finishes only when the desired sync level has been achieved.
An alternative solution could be to receive notification through the WAL
stream. Use pg_revclogical to connect to a replication slot configured with
wal2json. Then you can send notifications with pg_logical_emit_message()
for instance which even allows you to send notifications from aborted
transactions.
Setup:
SELECT * FROM pg_create_logical_replication_slot('w2j', 'wal2json');
Here is a sender:
CREATE SCHEMA notify;
CREATE OR REPLACE FUNCTION notify.send(
transactional BOOLEAN,
channel TEXT,
message TEXT
) RETURNS PG_LSN AS $$
SELECT pg_logical_emit_message(
transactional,
'NOTIFY_'||channel,
jsonb_build_object(
'txn_start', now()::TEXT,
'pid', pg_backend_pid(),
'message', message
)::TEXT
);
$$ LANGUAGE sql VOLATILE;
And something like this as a receiver / distributor:
sudo -iu postgres /usr/lib/postgresql/$VERSION/bin/pg_recvlogical \
-d 'port=5432 dbname=your_database' \
-S w2j \
-f - \
-F 0 \
--start \
-o include-xids=1 \
-o include-timestamp=1 \
-o add-tables=notify.\* \
-o format-version=2 |
jq 'select(.action=="M" and (.prefix | test("^NOTIFY_"))) |
. as $x |
.content |
fromjson + {
"channel": ($x.prefix | .[7:]),
"xid": $x.xid,
"txn_commit": $x.timestamp,
"transactional": $x.transactional
}'
Note, this is not a 100% replacement. The semantics are slightly different.
You also need a replication slot which comes with its own maintenance cost.
But maybe it's worth a try.
Have fun,
Torsten
On Tue, Apr 30, 2024 at 12:05 PM Dimitris Zenios
wrote:
> Hi,
>
> I am measuring a very simple case of pg_notify in an after update trigger.
> The trigger is the following:
>
> CREATE
> OR REPLACE FUNCTION audit_event() RETURNS TRIGGER AS
> $$
> BEGIN
> PERFORM pg_notify('user', 'hello world');
> RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
> and configured on the table with the following sql
>
> CREATE CONSTRAINT TRIGGER trigger_test AFTER UPDATE ON pgbench_accounts
> FOR EACH ROW EXECUTE PROCEDURE audit_event()
>
> I am running two benchmarks. One with the pg_notify on the trigger enabled
> and one with the notify commented out.
>
> The command is the following:
> pgbench -f /tmp/update_bench.sql -c 10 -j 10 -t 100 benchdb
>
> And the content of the update_bench.sql are the following
>
> \set aid random(1, 10 * :scale)
> \set delta random(-5000, 5000)
> BEGIN;
> UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
> END;
>
> Results are
>
> - With pg_notify disabled
>
> pgbench (16.1)
> starting vacuum...end.
> transaction type: /tmp/update_bench.sql
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 10
> maximum number of tries: 1
> number of transactions per client: 100
> number of transactions actually processed: 1000/1000
> number of failed transactions: 0 (0.000%)
> latency average = 11.744 ms
> initial connection time = 15.616 ms
> tps = 851.531991 (without initial connection time)
>
> - With pg_notify enabled
>
> pgbench (16.1)
> starting vacuum...end.
> transaction type: /tmp/update_bench.sql
> scaling factor: 1
> query mode: simple
> number of clients: 10
> number of threads: 10
> maximum number of tries: 1
> number of transactions per client: 100
> number of transactions actually processed: 1000/1000
> number of failed transactions: 0 (0.000%)
> latency average = 56.927 ms
> initial connection time = 11.182 ms
> tps = 175.664989 (without initial connection time)
>
> There is a huge drop in TPS from 851 to 175.
>
> I also noticed that if I run the test with a single connection -c 1 then
> the results are nearly identical which makes me assume that this is a
> contention that occurs between multiple connections.
>
> Thanks
>
>
slightly unexpected result
Hi, imagine a simple table with 1 row =# table tf; i | x ---+ 1 | xx (1 row) And this query: with x as (update tf set i=i+1 returning *) , y as (update tf set x=x||'yy' returning *) select * from x,y; My PG14 gives this result i | x | i | x ---+---+---+--- (0 rows) To me that was a bit surprising. I would have expected it to fail with something like "can't update the same row twice in the same command". If I check the table content after the query I see the i=i+1 part was executed. Is this expected behavior? Thanks, Torsten
Re: Corruption or wrong results with 14.10?
On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) < daniel.westerm...@dbi-services.com> wrote: > smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group > by crart_id, chemin having count(*) > 1; > crart_id | chemin | count > -++--- > 3505463 | @ | 2 > 473731 | @ | 2 > 1063238 | @ | 2 > 1468088 | @ | 2 > 462723 | @ | 2 > 1099378 | @ | 2 > > > I think I know what I have to do. > If you have moved the database from a system with glibc <2.28 to >=2.28, that could be the reason. https://wiki.postgresql.org/wiki/Collations
query plan
Hi, This is part of a query plan: Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626) -> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195 width=608) -> Nested Loop Left Join (cost=6.79..2876.77 rows=102 width=373) -> Nested Loop Left Join (cost=1.90..1965.51 rows=102 width=361) -> Bitmap Heap Scan on ... (cost=4.89..8.91 rows=2 width=28) -> Hash Left Join (cost=18.95..42.61 rows=3 width=243) -> Hash Left Join (cost=18.94..42.59 rows=3 width=203) -> Hash (cost=0.00..0.00 rows=1 width=48) -> Memoize (cost=0.58..4.59 rows=1 width=172) What I don't understand is this. The left node of the join is expected to return 102 rows. The right node 3. How can this result in >1e9 rows? The query involved way further down a partitioned table with 2 partitions, one pretty big in the 1e9 rows range, the other practically empty. The big partition had been analyzed before. But the partitioned table and the empty partition never. After analyzing them all was well. I am just curious to understand how that number is calculated. This is PG14. Thanks.
partitioning
Hi, I have an old, several TB table. That table has a child table (table inheritance) which is much smaller. Each row represents a certain process. The original idea was while the process is in progress it is stored in the small table. Once it has reached its final state, it is moved to the big one. Now I want to convert this to the declarative partitioning scheme and in that process introduce a new partition for finished processes. The fact that a process is finished is represented by the boolean column is_sold. The table has also a timestamp column called purchase_time. The new partitioning should be as follows CREATE TABLE ... (...) PARTITION BY RANGE (is_sold, purchase_time); Then I want to reattach the original small table with the unfinished processes like so: ALTER TABLE ... ATTACH PARTITION original_small_table FOR VALUES FROM (false, '-infinity') TO (false, 'infinity'); Reattach the big table like so: ALTER TABLE ... ATTACH PARTITION original_big_table FOR VALUES FROM (true, '-infinity') TO (true, 'tomorrow'); And create a new default partition for the rest, the newly finished processes: CREATE TABLE ... PARTITION OF ... DEFAULT; Both is_sold and purchase_time have a NOT NULL constraint. The small table also has a check constraint CHECK(NOT is_sold). Now, the documentation ( https://www.postgresql.org/docs/14/ddl-partitioning.html) says, if the table that's going to be attached as a partition has a constraint that ensures the partition boundaries, the check while attaching it can be avoided: > Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be > attached that matches the expected partition constraint, as illustrated above. That way, the system will be able to skip the > scan which is otherwise needed to validate the implicit partition constraint. So, initially I thought that check constraint should be enough. But it was not. Then I added this constraint to the small table: ALTER TABLE original_small_table ADD CONSTRAINT partition_boundaries CHECK((false, '-infinity')<=(is_sold, purchase_time) AND (is_sold, purchase_time)<(false, 'infinity')) NOT VALID; And validated it. When this is being attached as a partition, I still can see the process is reading the entire table. What am I missing? What should the check constraint look like in my case to match the partition constraint? This is PG 14. Thanks, Torsten
Exclusion constraint with negated operator?
Hi, I wanted to create an exclusion constraint like EXCLUDE (c1 WITH =, c2 with <>) This gives an error: operator <>(integer,integer) is not a member of operator family "integer_ops" I can resolve that by using gist and the btree_gist extension. My question is can I somehow express something like EXCLUDE (c1 WITH =, c2 with NOT =) It seems that's not possible at the moment. But is there any obstacle in principle or is it just not implemented? We have a ton of overlap operators (geometric, ranges, arrays) but none for the opposite. Sometimes that's useful for exclusion constraints. Thanks, Torsten
Re: ERROR: deadlock detected
On Tue, Feb 7, 2023 at 12:47 PM Matthias Apitz wrote: > > > We saw the following message in the file postgres-serverlog.error: > > 2023-01-24 17:16:16.578 CET [17468] ERROR: deadlock detected > 2023-01-24 17:16:16.578 CET [17468] DETAIL: Process 17468 waits for > ShareLock on transaction 90776649; blocked by process 17724. > Process 17724 waits for ShareLock on transaction 90776650; blocked > by process 17468. > Process 17468: fetch hc_d03geb > Process 17724: fetch hc_d02ben > 2023-01-24 17:16:16.578 CET [17468] HINT: See server log for query > details. > 2023-01-24 17:16:16.578 CET [17468] CONTEXT: while locking tuple (948,45) > in relation "d03geb" > 2023-01-24 17:16:16.578 CET [17468] STATEMENT: fetch hc_d03geb > > The process numbers are internal ones of the PostgreSQL server and not > the Linux PID, correct? > If you run this on linux, then the process numbers are the PIDs of the postgres backend processes. They are not the PIDs of your application but what would be returned by `pg_backend_pid()` or what you see in `pg_stat_activity`. This piece (948,45) is the CTID of the tuple where the deadlock occurred. If you really want to find out how the deadlock came about, you could use pg_waldump. You search for the transaction ids and figure out what they were doing. Torsten
plpgsql: ambiguous column reference in ON CONFLICT clause
Is there a way to prevent this from happening? I know I can use the PK constraint name or rename the OUT variable i. The question is can this be resolved while keeping the arbiter inference and the variable name. CREATE TABLE x.x ( i INT PRIMARY KEY ); CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO x.x(i) SELECT p_i ON CONFLICT (i) DO NOTHING; END $$; postgres=# select * from x.ins(1); ERROR: column reference "i" is ambiguous LINE 3: ON CONFLICT (i) DO NOTHING ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: INSERT INTO x.x(i) SELECT p_i ON CONFLICT (i) DO NOTHING CONTEXT: PL/pgSQL function x.ins(integer) line 3 at SQL statement The conflicting variable is the OUT parameter of the function. Normally, I'd suggest to fully qualify the name but the following or similar is a syntax error: INSERT INTO x.x(i) AS t SELECT p_i ON CONFLICT (t.i) DO NOTHING; According to the documentation in https://www.postgresql.org/docs/current/plpgsql-implementation.html: > Query parameters will only be substituted in places where they are syntactically permissible. and > Another way to understand this is that variable substitution can only insert data values into an SQL command; it cannot dynamically change which database objects are referenced by the command. After reading this I am wondering if the current behavior is actually a bug. Thanks, Torsten
minor bug
Hi, not sure if this is known behavior. Server version is 14.6 (Debian 14.6-1.pgdg110+1). In a PITR setup I have these settings: recovery_target_xid = '852381' recovery_target_inclusive = 'false' In the log file I see this message: LOG: recovery stopping before commit of transaction 852381, time 2000-01-01 00:00:00+00 But: postgres=# select * from pg_last_committed_xact(); xid | timestamp | roident +---+- 852380 | 2023-01-16 18:00:35.054495+00 | 0 So, the timestamp displayed in the log message is certainly wrong. Thanks, Torsten
Re: PITR and instance without any activity
On Mon, Jan 9, 2023 at 10:59 AM Adrien Nayrat wrote: > > * We can't perform PITR on a cluster without any activity since 13 > * It seems creating restore point doesn't record a timestamp in wal. > I have a cron job that runs this every 5 minutes: SELECT txid_current() WHERE (pg_last_committed_xact()).timestamp+'5min'::INTERVAL < now() Combine that with a suitable value for archive_timeout.
Re: Replicating an existing (huge) database
You can set up wal archiving and set restore_command before you start the replica. Then you can use pg_basebackup with `-Xnone --no-slot`. Alternatively or in combination, use a quicker backup as Laurenz explained. On Mon, Nov 28, 2022 at 11:17 AM Srinivasa T N wrote: > Hi All, >I am using postgresql 12. As part of streaming replication setup, I > run pg_basebackup on the slave which copies the database from master to > slave. But the database is huge and it takes around 2 to 3 days for the > pg_basebackup to finish. When pg_basebackup is running, a huge number of > wal files are generated on the master which occupies a lot of space (even > though it is for 2-3 days, disk space is low). Is there any other way to > start replication without using pg_baseback? > > Regards, > Seenu. >
Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
This is what happens: WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT w.id, w.name, w.elev FROM weather_stations AS w WHERE elev > (SELECT x.elev FROM elev_Tlse_Blagnac AS x WHERE w.id BETWEEN 3100 and 3199); id| name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 rows) Note the use of aliases, w and x. You are using a correlated subquery. On Tue, Mar 22, 2022 at 10:46 AM PALAYRET Jacques wrote: > Hello, > > # Let's consider a table defined as follows : > CREATE TABLE weather_stations( > id integer, > name varchar(30), > elev integer > ) ; > > # After loading, the content : > id| name | elev > --+--+-- > 31069001 | TOULOUSE-BLAGNAC | 151 > 31006001 | ALBIAC AGGLOMERATION | 289 > 31010001 | LUCHON-ANTIGNAC | 599 > 50003001 | AGON-COUTAINVILLE|2 > 50195001 | GATHEMO | 330 > (5 lignes) > > ### With CTE : > # I'm suprised by the following result, the behavior of PostgreSQL ; is > that a bug ? : > = Statement 1 : = > WITH elev_Tlse_Blagnac AS ( > SELECT elev FROM weather_stations WHERE id=31069001 > ) > SELECT id, name, elev FROM weather_stations > WHERE elev > ( > SELECT elev FROM elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 > ) ; > id| name | elev > --+--+-- > 31006001 | ALBIAC AGGLOMERATION | 289 > 31010001 | LUCHON-ANTIGNAC | 599 > (2 lignes) > # According to me, the previous result is an error, because the > parentheses are not taken into account. > The column id is not part of elev_Tlse_Blagnac. > > > # The same result as following, which is of course OK : > = Statement 2 : = > WITH elev_Tlse_Blagnac AS ( > SELECT elev FROM weather_stations WHERE id=31069001 > ) > SELECT id, name, elev FROM weather_stations > WHERE elev > ( > SELECT elev FROM elev_Tlse_Blagnac > ) > AND id BETWEEN 3100 and 3199 > ; > id| name | elev > --+--+-- > 31006001 | ALBIAC AGGLOMERATION | 289 > 31010001 | LUCHON-ANTIGNAC | 599 > (2 lignes) > > > ### Same weird behavior with subquery in FROM clause : > # NOT OK (according to me), because the parentheses are not taken into > account : > = Statement 3 : = > SELECT id, name, elev FROM weather_stations > WHERE elev > ( > SELECT elev FROM (SELECT elev FROM weather_stations WHERE id=31069001) > elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 > ) ; > id| name | elev > --+--+-- > 31006001 | ALBIAC AGGLOMERATION | 289 > 31010001 | LUCHON-ANTIGNAC | 599 > (2 lignes) > > # OK, the parentheses are taken into account because there is no confusion > with the column id (elev_Tlse_Blagnac has a column named id) : > = Statement 4 : = > SELECT id, name, elev FROM weather_stations WHERE elev > ( > SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) > elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 > ) ; > id| name | elev > --+--+-- > 31006001 | ALBIAC AGGLOMERATION | 289 > 31010001 | LUCHON-ANTIGNAC | 599 > 50195001 | GATHEMO | 330 > (3 lignes) > > # OK (of course) : > = Statement 5 : = > SELECT id, name, elev FROM weather_stations WHERE elev > ( > SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) > elev_Tlse_Blagnac > ) > AND id BETWEEN 3100 and 3199 > ; > id| name | elev > --+--+-- > 31006001 | ALBIAC AGGLOMERATION | 289 > 31010001 | LUCHON-ANTIGNAC | 599 > (2 lignes) > > > Is that a PostgreSQL bug or not, statement 1 or statement 3 (yes according > to me) ? > > > Regards > - Météo-France - > PALAYRET JACQUES > DCSC/GDC > jacques.palay...@meteo.fr > Fixe : +33 561078319 >
AWS vs GCP storage
Hi, I have a few databases with several TB-sized tables. We recently moved one of those databases from AWS EC2 to GCP. Today I ran VACUUM FREEZE on those tables and every time I saw our application freezing up (and throwing tons of errors) for a few minutes right after the message about the end of vacuum (duration: 30182257.392 ms statement: vacuum freeze verbose ...). We never saw anything like that on AWS. The database is 9.6. At the moment I have no details about the storage configuration on GCP. The machine was set up by another department. Any suggestions on why that is happening and how to prevent it would be appreciated. Thanks, Torsten
Re: How to split normal and overtime hours
WITH x AS ( SELECT * , sum(hours) OVER w AS s FROM hours WINDOW w AS (PARTITION BY person ORDER BY job_id) ) SELECT * , greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS regular , hours - greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS overtime FROM x WINDOW w AS (PARTITION BY person ORDER BY job_id) On Sun, Feb 13, 2022 at 1:57 PM Andrus wrote: > Hi! > > Thank you. In this result, regular and overtime columns contain running > totals. > > How to fix this so that those columns contain just hours for each job? > > sum on regular column should not be greater than 120 per person. > > sum of regular and overtime columns must be same as sum of hours column > in hours table for every person. > > Andrus. > 13.02.2022 14:46 Torsten Förtsch kirjutas: > > something like > > SELECT * > , least(sum(hours) OVER w, 120) AS regular > , greatest(sum(hours) OVER w - 120, 0) AS overtime > FROM hours > WINDOW w AS (PARTITION BY person ORDER BY job_id); > > job_id | person | hours | regular | overtime > ++---+-+-- > 2 | bill |10 | 10 |0 > 5 | bill |40 | 50 |0 > 8 | bill |10 | 60 |0 > 10 | bill |70 | 120 | 10 > 11 | bill |30 | 120 | 40 > 13 | bill |40 | 120 | 80 > 15 | bill |10 | 120 | 90 > 4 | hugo |70 | 70 |0 > 7 | hugo | 130 | 120 | 80 > 1 | john |10 | 10 |0 > 3 | john |50 | 60 |0 > 6 | john |30 | 90 |0 > 9 | john |50 | 120 | 20 > 12 | john |30 | 120 | 50 > 14 | john |50 | 120 | 100 > > > On Sun, Feb 13, 2022 at 12:47 PM Andrus wrote: > >> Hi! >> >> Hours table contains working hours for jobs: >> >> create table hours ( >> jobid integer primary key, -- job done, unique for person >> personid char(10) not null, -- person who did job >> hours numeric(5,2) not null -- hours worked for job >> ) >> >> Hours more than 120 are overtime hours. >> >> How to split regular and overtime hours into different columns using >> running total by job id and partition by person id? >> >> For example, if John did 3 jobs 1, 2,3 with 90, 50 and 40 hours (total >> 180 hours) for each job correspondingly, result table should be: >> >> personidjobid normal_hours overtime_hours >> john1 90 0 >> john2 30 20 >> john3 0 40 >> >> sum on normal_hours column should not be greater than 120 per person. >> >> sum of normal_hours and overtime_hours columns must be same as sum of >> hours column in hours table for every person. >> >> Note that since hours running total becomes greater than 120 in job 2, >> job 2 hours should appear in both hours columns. >> >> Maybe window functions can used. >> >> Andrus. >> >
Re: How to split normal and overtime hours
something like SELECT * , least(sum(hours) OVER w, 120) AS regular , greatest(sum(hours) OVER w - 120, 0) AS overtime FROM hours WINDOW w AS (PARTITION BY person ORDER BY job_id); job_id | person | hours | regular | overtime ++---+-+-- 2 | bill |10 | 10 |0 5 | bill |40 | 50 |0 8 | bill |10 | 60 |0 10 | bill |70 | 120 | 10 11 | bill |30 | 120 | 40 13 | bill |40 | 120 | 80 15 | bill |10 | 120 | 90 4 | hugo |70 | 70 |0 7 | hugo | 130 | 120 | 80 1 | john |10 | 10 |0 3 | john |50 | 60 |0 6 | john |30 | 90 |0 9 | john |50 | 120 | 20 12 | john |30 | 120 | 50 14 | john |50 | 120 | 100 On Sun, Feb 13, 2022 at 12:47 PM Andrus wrote: > Hi! > > Hours table contains working hours for jobs: > > create table hours ( > jobid integer primary key, -- job done, unique for person > personid char(10) not null, -- person who did job > hours numeric(5,2) not null -- hours worked for job > ) > > Hours more than 120 are overtime hours. > > How to split regular and overtime hours into different columns using > running total by job id and partition by person id? > > For example, if John did 3 jobs 1, 2,3 with 90, 50 and 40 hours (total 180 > hours) for each job correspondingly, result table should be: > > personidjobid normal_hours overtime_hours > john1 90 0 > john2 30 20 > john3 0 40 > > sum on normal_hours column should not be greater than 120 per person. > > sum of normal_hours and overtime_hours columns must be same as sum of > hours column in hours table for every person. > > Note that since hours running total becomes greater than 120 in job 2, job > 2 hours should appear in both hours columns. > > Maybe window functions can used. > > Andrus. >
Re: WAL Archiving and base backup
On Wed, Jan 12, 2022 at 1:22 PM Issa Gorissen wrote:
> So I have this question, how to script the making of base backup for
> transfer on the slave server when the two SQL functions must be called
> in the same connection, in Bash for example; is this doable?
>
Not sure if I understand the problem correctly but if you are asking how to
open a connection to the database and keep it open while doing something
else in bash, then you could use "coproc" for instance. I sometimes use
this function:
coproc psql -XAtF $'\t' service="$srv"
pg () {
local sql exp
sql="$1"
[ "$2" ] && { sql="$2"; exp="$1"; }
echo "$sql" >&${COPROC[1]}
read -u ${COPROC[0]} || return
[ "$exp" ] || return 0
[ "$REPLY" = "$exp" ] || return 64
return 0
}
And here is some usage
local TMOUT=1
pg BEGIN 'BEGIN;'
This sends a BEGIN command and expects the word BEGIN as reply.
if pg 'LOCK TABLE' '
LOCK TABLE some_table
IN SHARE ROW EXCLUSIVE MODE NOWAIT;
';
then
:
elif (( $? > 128 )); then # read timeout exceeded
die "Cannot lock some_table";
else
die "Unexpected error while locking some_table";
fi
In the example above a table is locked with NOWAIT. Bash's read timeout is
set to 1sec. If that's exceeded because the lock is not obtained, read
comes back with status>128.
unset TMOUT
pg '
SELECT coalesce(min(id), -1)
, coalesce(max(id), -1)
FROM some_table'\;
Now we want to read some data. So, TMOUT is unset. The REPLY variable will
have the answer.
IFS=$'\t' read mn mx <<<"$REPLY"
And this is how to split the reply into 2 bash variables, mn and mx.
At the end of the transaction then
pg 'COMMIT' 'COMMIT;'
And send \q to finish psql. If "set -e" mode is active, make sure to negate
the result.
# expecting read to fail after \q. Hence the negation.
! pg '\q'
In simpler cases, when you just want to push commands to psql, you can also
use this:
exec {PSQL}> >(psql ...)
Note there is a blank between the 2 >. This is important.
Then
echo >&$PSQL 'create table tf ();'
echo >&$PSQL 'drop table tf;'
Does this help?
Re: Listen and notify in psql process
On Thu, Jun 17, 2021 at 1:04 PM Sakshi Jain
wrote:
How to listen from within a psql process and get the payloads?
>
> Do Postgresql have any such a mechanism where in a session a process send
> a "listen " sql command and then gets a message if someone in the
> other session issued a "notify ".
>
> Please provide an example of how to do this.
>
> I am looking for listen to return some thing when a notify has been issued.
>
Here is an example in perl. Basically you issue the LISTEN command. Then
you sit and watch the file descriptor to become ready. You do that outside
of a transaction. Once the descriptor is ready you call your driver's
version of notification read function until it comes back empty. That is
when you go back and wait for the next arrival.
$dbh->do("LISTEN channel");
my $sel = IO::Select->new;
$sel->add($dbh->{pg_socket});
while ($sel->can_read) {
while (my $notify = $dbh->pg_notifies) {
my ($name, $pid, $payload) = @$notify;
do_something($payload);
}
}
>
Re: Inserts restricted to a trigger
Have you tried session_user? create function xx() returns table (cur text, sess text) security definer language sql as $$ select current_user::text, session_user::text; $$; Then log in as different user and: => select (xx()).*; cur| sess --+--- postgres | write On Tue, Jun 18, 2019 at 6:30 PM Miles Elam wrote: > That seems straightforward. Unfortunately I also want to know the > user/role that performed the operation. If I use SECURITY DEFINER, I get > the superuser account back from CURRENT_USER, not the actual user. > > Sorry, should have included that in the original email. How do I restrict > access while still retaining info about the current user/role? > > > On Mon, Jun 17, 2019 at 5:47 PM wrote: > >> Adrian Klaver wrote: >> >> > On 6/17/19 4:54 PM, Miles Elam wrote: >> > > Is there are way to restrict direct access to a table for inserts but >> > > allow a trigger on another table to perform an insert for that user? >> > > >> > > I'm trying to implement an audit table without allowing user tampering >> > > with the audit information. >> > >> > Would the below not work?: >> > CREATE the table as superuser or other privileged user >> > Have trigger function run as above user(use SECURITY DEFINER) >> >> and make sure not to give any other users insert/update/delete >> permissions on the audit table. >> >> > > Thanks in advance, >> > > >> > > Miles Elam >> > >> > -- >> > Adrian Klaver >> > adrian.kla...@aklaver.com >> >> >>
unexpected behavior with pglogical -- bug?
Hi, out of curiosity I created the following setup, all with 9.6 and pglogical. D1 is configured as provider with a replication set that contains only 1 table. Only inserts are replicated. D2 is configured as subscriber for that replication set. Replication works, all inserts on D2 arrive also on D2. Now, I add the following always firing trigger to the table: CREATE OR REPLACE FUNCTION notify.trgfn () RETURNS trigger AS $def$ BEGIN PERFORM pg_notify(NEW.channel, NEW.msg); RETURN NULL; END $def$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE INSERT ON notify.notify FOR EACH ROW EXECUTE PROCEDURE notify.trgfn(); ALTER TABLE notify.notify ENABLE ALWAYS TRIGGER trg; As you can see, the trigger function should prevent the actual insert and only call pg_notify(). In principle this works but there is a catch. Notifications generated this way are only delivered after another notification genuinely generated on the subscriber node. The channel of this notification does not matter. If I replace PERFORM pg_notify() by RAISE NOTICE I see the message immediately in the log. First I thought this is related to session_replication_role=replica. So, I tried the direct insert on D2 with this setting using psql. The notification was fired immediately. Also, whether the trigger prevents or allows the actual insert does not matter. I tried to create the trigger function as SECURITY DEFINER and with a specific search_path. That didn't help either. By now I am thinking there must be something missing in pglogical. Thanks, Torsten
Re: SQL queries not matching on certain fields
Broken index?
I had a similar problem a while ago. We were streaming a DB from a Debian
box to an alpine docker image. The underlying system libraries were a
little different and that resulted in broken index behavior.
On Wed, Apr 3, 2019 at 3:06 PM Felix Ableitner wrote:
> Hello,
>
> I'm having a very strange problem with the Postgres database for my
> website. Some SQL queries are not matching on certain fields. I am running
> these commands via the psql command.
>
> Here is a query that works as expected:
>
> # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='emma';
> id | preferredUsername
> ---+---
> 48952 | emma
> 58672 | emma
> (2 rows)
>
> The following query should work as well, because the username exists. But
> in fact, it consistently returns nothing:
>
> # SELECT id, "preferredUsername" FROM actor WHERE
> "preferredUsername"='mailab';
> id | preferredUsername
> +---
>
> (0 rows)
>
> There are some workarounds which fix the WHERE statement, all of the
> following work as expected:
>
> SELECT id, "preferredUsername" FROM actor WHERE
> trim("preferredUsername")='mailab';
> SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE
> 'mailab'; SELECT id, "preferredUsername" FROM actor WHERE
> md5("preferredUsername")=md5('mailab');
>
>
> Now you might think that there is something wrong with the encoding, or
> the field contains some extra whitespace. But SHOW SERVER_ENCODING and SHOW
> SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected.
> And I checked the individual bytes with get_byte(), all of them are in the
> range 97-122.
>
> About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS
> (see below for all versions etc). I had this problem before on the same
> setup, so I did an export to text file with pg_dump, and imported into a
> completely new database with psql. That fixed the problem for a few days,
> but it came back soon after.
>
> The problem only seems to affect one or two specific columns, and only a
> few specific rows in those columns. Most other rows work normally. Affected
> columns also randomly start working again after a few days, and other
> columns get affected. I havent noticed any kind of pattern.
>
> You can find the table definition here:
> https://gitlab.com/snippets/1840320
>
> Version info:
>
> Postgres Docker Image: postgres:10.7-alpine
> Docker version: 18.09.2
> OS: Ubuntu 18.04.2
>
> Please tell me if you have any idea how to fix or debug this. I already
> asked multiple people, and no one has a clue what is going on.
>
> Best,
> Felix Ableitner
>
Re: conditionally terminate psql script
On Mon, Dec 17, 2018 at 2:07 PM wrote: > many thanks -- too bad I am still using 9.3 > > not sure if it has been mentioned in the thread so far. But according to the docs, 9.3 psql does support the \gset command. So you should be able to do something like this: select case when exists (select 1 from pg_namespace where nspname='tf') then '\echo schema tf exists \\ \q' else '\echo schema does not exist -- creating ...' end as cmd\gset :cmd create schema tf; create table tf.i(i int);
Is there a way to speed up WAL replay?
Hi, I am working on restoring a database from a base backup + WAL. With the default settings the database replays about 3-4 WAL files per second. The startup process takes about 65% of a CPU and writes data with something between 50 and 100 MB/sec. Is there a way to speed that up? The disk can easily sustain 400-500 MB/sec. Thanks, Torsten
Re: backend_xmin in pg_stat_replication
Thanks a lot! So, the correct calculation is like this: select application_name, txid_snapshot_xmin(txid_current_snapshot()), backend_xmin::TEXT::BIGINT, (txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT)%(2^32)::BIGINT from pg_stat_replication; application_name | txid_snapshot_xmin | backend_xmin | ?column? --++--+-- xx | 6960964080 | 2665996642 | 142 That makes more sense. On Mon, Oct 1, 2018 at 5:32 PM Andres Freund wrote: > Hi, > > On 2018-10-01 12:20:26 +0200, Torsten Förtsch wrote: > > if I understand it correctly, backend_xmin in pg_stat_replication is the > > xmin that's reported back by hot_standby_feedback. Given there are no > > long-running transactions on the replica, I presume that value should be > > pretty close to the xmin field of any recent snapshots on the master. > This > > is true for all my databases but one: > > > > select application_name, > >txid_snapshot_xmin(txid_current_snapshot()), > >backend_xmin::TEXT::BIGINT, > > > > txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT > > from pg_stat_replication; > > > > application_name | txid_snapshot_xmin | backend_xmin | ?column? > > --++--+ > > xx | 6 957 042 833 | 2 662 075 435 | 4 294 967 > 398 > > I don't think the calculation you're doing here is correct. > backend_xmin is an xid (max 2^32-1), whereas txid_snapshot_xmin returns > an xid *with* epoch (max 2^64-1). What you're measuring here is simply > the fact that the xid counter has wrapped around. > > Greetings, > > Andres Freund >
vacuum question
Hi, I have a table with a really small number of rows, usually about 1500, sometimes may be up to 5000. The usage pattern of that table is such that rows are inserted and kept for a while, mostly seconds or minutes but theoretically up to 1 year. After that they are deleted. No updates, just insert, delete. The table is pretty actively written, sometimes >100 transactions per second. Although the table is frequently auto-vacuumed, its size is growing over time. The size of the table corresponds to the remaining number of pages in the autovacuum log. pages: 0 removed, 30069 remain, 0 skipped due to pins, 29985 skipped frozen tuples: 136 removed, 1643 remain, 15 are dead but not yet removable buffer usage: 44327 hits, 0 misses, 21 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.036 MB/s system usage: CPU 0.06s/0.05u sec elapsed 4.59 sec What does "29985 skipped frozen" mean? I presume these skipped buffers is the table bloat. My way to get rid of the bloat is to cluster the table. That takes less than half a second and the next autovacuum log then looks like this: pages: 0 removed, 3493 remain, 0 skipped due to pins, 0 skipped frozen tuples: 0 removed, 58873 remain, 57470 are dead but not yet removable buffer usage: 9425 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU 0.02s/0.03u sec elapsed 1.01 sec For a number of autovacuum cycles "skipped frozen" remains 0 until eventually: pages: 0 removed, 4822 remain, 0 skipped due to pins, 4433 skipped frozen tuples: 227 removed, 2609 remain, 13 are dead but not yet removable buffer usage: 5269 hits, 0 misses, 315 dirtied avg read rate: 0.000 MB/s, avg write rate: 2.065 MB/s system usage: CPU 0.03s/0.01u sec elapsed 1.19 sec Is there anything I can do to prevent that bloat from accumulating in the first place? Another thing that seems odd is the number of dead but not removable tuples in the record in the middle. Sifting through the log, I can see numbers up to 8. One of our replicas is configured with hot_standby_feedback. Can that be the culprit? This is 9.6.10. Thanks, Torsten
[no subject]
Hi, is there a way to find if a certain page in a data file is referenced by a btree index? I found a few completely empty pages in one of my tables. I am not sure if that's corruption or just bloat. Now I am thinking I could use an index, the PK for instance, and see if it references these pages. Thanks, Torsten
Re: functions with side effect
On Thu, Jul 19, 2018 at 8:10 PM Tom Lane wrote:
> =?UTF-8?Q?Torsten_F=C3=B6rtsch?= writes:
> > I know that. My question was about the execution order of f1 and f2 in
> > "SELECT f1(), f2()". In theory they can be executed in any order. But
> since
> > the side effect in nextval determines the result of currval, I am asking
> if
> > that order is well-defined or considered an implementation detail like
> in C.
>
> The current implementation evaluates select-list items left to right.
> I doubt we'd be eager to change that, since there are surely many
> applications that depend on that behavior, whether it's formally specified
> or not. But elsewhere in a query than the select target list, there are
> no guarantees, and there's lots of precedent for whacking around the
> evaluation order in e.g. WHERE.
>
> I'd be a little more wary with examples like your other one:
>
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>
> since there's an additional unspecified question there, which is
> whether the planner will "flatten" the sub-select. To put it more
> clearly, you'd be taking big risks with
>
> SELECT y, x FROM (VALUES (nextval('s'), currval('s'))) t(x, y);
>
> Right now it seems the nextval is done first, but I would not want to bet
> on that staying true in the future. [ experiments some more ... ]
> Actually, looks like we have a rule against flattening sub-selects whose
> targetlists contain volatile functions, so maybe you'd get away with that
> for the indefinite future too.
>
Thanks, this was actually a part of an insert statement I found in our
code. Something like
INSERT INTO ...
VALUES (nextval(), ..., 'string' || currval())
Just to be on the safe side I changed it to
INSERT INTO ...
SELECT next.id, ..., 'string' || next.id
FROM nextval() next(id)
Re: functions with side effect
On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver
wrote:
> On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
> > Hi,
> >
> > assuming
> >
> > SELECT nextval('s'), currval('s');
> >
> > or
> >
> > SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
> >
> > is there any guarantee that the 2 output values are the same?
>
> Assuming you are only working in single session:
>
> https://www.postgresql.org/docs/10/static/functions-sequence.html
>
> "currval
>
> Return the value most recently obtained by nextval for this
> sequence in the current session. (An error is reported if nextval has
> never been called for this sequence in this session.) Because this is
> returning a session-local value, it gives a predictable answer whether
> or not other sessions have executed nextval since the current session did."
I know that. My question was about the execution order of f1 and f2 in
"SELECT f1(), f2()". In theory they can be executed in any order. But since
the side effect in nextval determines the result of currval, I am asking if
that order is well-defined or considered an implementation detail like in C.
functions with side effect
Hi,
assuming
SELECT nextval('s'), currval('s');
or
SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
is there any guarantee that the 2 output values are the same?
Thanks,
Torsten
How to drop a value from an ENUM?
Hi, I am absolutely sure a certain value of one of my ENUM types is not used in the entire database. Now I am asked to drop that value. Unfortunately, there is no ALTER TYPE DROP VALUE. On my development box I tried delete from pg_enum where enumtypid='my_type_name'::regtype and enumlabel='my_label' It worked and I could not find any adverse effects. Given the value is not used anywhere, is this a save command? Thanks, Torsten
Re: array_agg to array
On Wed, May 16, 2018 at 8:14 AM, Philipp Kraus <
philipp.kr...@tu-clausthal.de> wrote:
> Hello,
>
> I have got a function with a reg expr to split chemical formulas e.g. H2O
> -> H2 O.
>
> CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS
> $$
> select array_agg(i::text) as e from ( select unnest( regexp_matches(
> $1, '[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') ) )
> i;
> $$ LANGUAGE SQL IMMUTABLE;
>
> For H2O I get an array with {(H2),(O)}
> How I can return the inner elements as text, I would like to get {H2,O}
> without round brackets?
>
like this?
postgres=# select array_agg(i[1]) as e from regexp_matches( 'H2O',
'[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') t(i);
e
{H2,O}
(1 row)
dblink surprise
Hi,
according to the documentation, dblink_send_query sends the query to be
executed asynchronously. I tried this out in the following function and it
works as expected:
CREATE OR REPLACE FUNCTION t_par () RETURNS TABLE (
tx_time TIMESTAMP,
end_time TIMESTAMP
) AS $def$
DECLARE
v_q RECORD;
BEGIN
FOR v_q IN
WITH jobs(cn) AS (
VALUES ('c1'), ('c2')
)
, conn AS (
SELECT *, 1/(dblink_connect(cn, 'dbname=postgres
port=5440')='OK')::INT AS connstatus
FROM jobs
)
SELECT conn.*, 1/q.status AS sendstatus
FROM conn
CROSS JOIN LATERAL dblink_send_query(conn.cn,
$$
select now(), pg_sleep(3), clock_timestamp()
$$) q(status)
LOOP
RETURN QUERY
SELECT tb.tx_time, tb.end_time
FROM dblink_get_result(v_q.cn) tb(
tx_time TIMESTAMP,
dummy TEXT,
end_time TIMESTAMP
);
PERFORM dblink_disconnect(v_q.cn);
END LOOP;
END
$def$ LANGUAGE plpgsql;
# select * from t_par();
tx_time | end_time
+
2017-11-21 21:14:35.593741 | 2017-11-21 21:14:38.597384
2017-11-21 21:14:35.595805 | 2017-11-21 21:14:38.599272
Both remote queries start at the same time and finish 3 seconds later.
Then I thought I can take it one step further and collect the results in
the same statement:
CREATE OR REPLACE FUNCTION t_seq () RETURNS TABLE (
tx_time TIMESTAMP,
end_time TIMESTAMP
) AS $def$
BEGIN
RETURN QUERY
WITH v_q AS (
WITH jobs(cn) AS (
VALUES ('c1'), ('c2')
)
, conn AS (
SELECT *, 1/(dblink_connect(cn, 'dbname=postgres
port=5440')='OK')::INT AS connstatus
FROM jobs
)
SELECT conn.*, 1/q.status AS sendstatus
FROM conn
CROSS JOIN LATERAL dblink_send_query(conn.cn,
$$
select now(), pg_sleep(3), clock_timestamp()
$$) q(status)
)
SELECT tb.tx_time, tb.end_time
FROM v_q
CROSS JOIN LATERAL dblink_get_result(v_q.cn) tb(
tx_time TIMESTAMP,
dummy TEXT,
end_time TIMESTAMP
);
PERFORM dblink_disconnect(jobs.cn)
FROM (VALUES ('c1'), ('c2')) jobs(cn);
END
$def$ LANGUAGE plpgsql;
regentmarkets=# select * from t_seq();
tx_time | end_time
+
2017-11-21 21:25:07.764467 | 2017-11-21 21:25:10.768032
2017-11-21 21:25:10.770409 | 2017-11-21 21:25:13.773907
Unfortunately, that does not work. The remote queries are started one after
the other.
Why?
This is PG 9.6.
BTW, is it somehow possible in plpgsql to catch a query cancellation or
backend termination request? That would be useful to propagate such
requests to remote queries.
Thanks,
Torsten
