On Fri, Jan 24, 2025 at 4:48 AM anlex N wrote:
> Hello Adrian, Laurenz. Have you tried postgresql's json in your everyday
> life? How fast is it? how is it than mongodb?
>
My honest opinion, JSON(B) in PG is great as data transfer format but not
so much for storage. Here is an example. Up to las
On Thu, Jan 16, 2025 at 2:02 PM Laurenz Albe
wrote:
> On Thu, 2025-01-16 at 15:25 +0300, anlex N wrote:
> > I have searched all archives, but have no results. Please help me.
>
> That's probably because the question doesn't make much sense.
> A single PostgreSQL instance (=cluster) enforces consi
PG normally splits table data into 1GB chunks. The number before the dot is
called the filenode. You can translate it into a table name by
select oid::regclass::text from pg_class where relfilenode='2474695';
I believe there is an option to change that chunk size but you'd have to
recompile Postg
On Fri, Nov 15, 2024 at 9:38 AM Philip Couling wrote:
> Is there a solid reason why adding a check constraint does not use
> existing indexes for validation.
>
> We are currently looking at partitioning a multi TB table leaving all
> existing data in place and simply attaching it as a partition t
Your point of divergence is in the middle of the 7718/00BF file. So,
you should have 2 such files eventually, one on timeline 1 and the other on
timeline 2.
Are you archiving WAL on the promoted machine in a way that your
restore_command can find it? Check archive_command and archive_mode on t
Thiemo,
it looks to me like you are using inheritance just to make sure your
SOURCES and TOPO_FILES tables have some common columns. If you are not
actually querying the TEMPLATE_TECH table and expect to see all the rows
from the other 2 tables in that one table combined, then you could use
CREATE
Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or
similar). The number 16665 is called the file node.
To get a list of file nodes for a specific database you can run:
SELECT oid::regclass::text, relfilenode FROM pg_class;
The /16384/ in the path represents the database.
Try perhaps something along these lines:
```
SELECT t.id, i.insight_id
FROM taxpayers AS t
JOIN LATERAL (
SELECT x.id AS insight_id
FROM insights AS x
WHERE x.taxpayer_id = t.id
AND x.year IS NOT NULL
ORDER BY year DESC
I guess this query comes back non-empty:
SELECT * FROM paliers JOIN tmp_limitcontrats USING(idcontrat) WHERE
qtep1 >= qtep2
This would then lead somewhere to this expression numrange(3,2)
Check out idpalier=805
On Tue, Aug 27, 2024 at 7:37 PM Adrian Klaver
wrote:
>
>
> On 8/27/24 10:29 AM, J
If you want to convert your table name into a timestamp, you don't need
substring or similar. This also works:
=# select to_date('table_part_p2024_08_08', '"table_part_p""_"MM"_"DD');
to_date
2024-08-08
(1 row)
But as Greg said, your strings are perfectly sortable.
On Thu, A
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}
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 m
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
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 sugge
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 typ
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 delim
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
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
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 a
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 ,
>>> mea
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 n
sues 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
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
increa
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
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
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
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 bi
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
> -++---
> 350546300
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 Le
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
on
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
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 907766
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
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,
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_x
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 p
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
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
t; 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
>
> SELE
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 | bi
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 probl
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 ".
>
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,
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 o
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 havin
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
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 ea
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
> >
Hi,
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
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
inser
Hi,
if I read https://www.postgresql.org/docs/9.6/static/sql-insert.html
correctly there can be only one conflict target or none at all.
What if I have a table with 3 unique constraints? If the first one is
violated by the insert I want to do nothing. If the 2nd is violated some
fields should be
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
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 re
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 (nex
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
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='m
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
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 RE
58 matches
Mail list logo
