Re: How to use the BRIN index properly?

[GF]

For membership/equality predicates (also partial, for multiple columns) you
could take a look at bloom indexes: they are quite efficient in terms of
space footprint, you can even choose how long is the signature for each
entry and how is distributed among the columns.
https://www.postgresql.org/docs/14/bloom.html
g



On Wed, 8 Feb 2023 at 23:15, Siddharth Jain  wrote:

> OK so in that case we are left with the B-Tree index.
>
> If the B-Tree index will be so large that it cannot fit in memory, then is
> it worth creating it at all? Are there any established patterns here?
>
> On Wed, Feb 8, 2023 at 1:21 PM Christophe Pettus  wrote:
>
>>
>>
>> > On Feb 8, 2023, at 13:17, Siddharth Jain  wrote:
>> >
>> > As I explained in my question that is indeed our dilemma. Our insertion
>> order will not be equal to index order. i.e., referring to your response:
>> >
>> > > who's data is added in the same order as the key in the BRIN index
>> >
>> > does NOT hold.
>>
>> A BRIN index is not a good choice in this case.  You can CLUSTER the data
>> on an index, but that's a one-time operation: PostgreSQL will not maintain
>> that order after the CLUSTER.  If the number of rows in the table at the
>> time of the CLUSTER is much larger than the number that are inserted
>> between CLUSTER operations, then a BRIN index might be useful, but
>> clustering a very large table is an expensive operation, and requires an
>> exclusive lock on the table while it is being done.
>
>

Re: [Extern] Re: Is a logical replication crash recovery safe?

[GF]

Markus,
Yesterday EDB published the (open source) pg_failover_slots extension,
aimed at pushing logical slot information on standbys: without it, that
information is kept on the master only, so you have to rebuild the logical
replication topology on switch/fail-overs.
Obviously, if the promoted server is outdated wrt to the lost master, you
could still have problems with subscriptions, although there some
configuration that tries to address that problem too
(standby_slots_min_confirmed).
For the moment being you can find the documentation at EDB's docs
, and
the sources at github.com/EnterpriseDB/pg_failover_slots. I hope the
extension will make quickly its way to PGDG as prebuilt packages.
Best,
g


On Tue, 24 Jan 2023 at 17:08, Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> > -Ursprüngliche Nachricht-
> > Von: Christophe Pettus 
> > Gesendet: Dienstag, 24. Januar 2023 16:48
> > An: Zwettler Markus (OIZ) 
> > Cc: pgsql-general@lists.postgresql.org
> > Betreff: [Extern] Re: Is a logical replication crash recovery safe?
> >
> >
> >
> > > On Jan 24, 2023, at 07:37, Zwettler Markus (OIZ) <
> markus.zwett...@zuerich.ch>
> > wrote:
> > >
> > > Is a logical replication crash recovery safe?
> >
> > You might want to give more detail about the scenario you are concerned
> about.
> > Changes that are received by a subscriber as part of logical replication
> are WAL-
> > logged, observe transaction semantics, and will be replayed if the
> subscriber
> > crashes and restarts.
> >
>
>
> Sorry. Let me refine.
>
> If I create a logical replication slot and use the replication slot to
> publish some tables.
>
> Will the information in the replication slot be safe for any subscription
> even if a crash recovery happened on the publisher node?
>
>
>

Re: Window function for get the last value to extend missing rows

[GF]

On Fri, 12 May 2023 at 13:04, Durumdara  wrote:

> Dear Members!
>
> I have a table with temperature measures.
> The data is coming from the PLC, but sometimes the period is "slipping",
> so the values are not correctly minute based.
>
> 03:00 10
> 03:02 12
> 03:03 11
> 03:05 13
>
> I have to make a virtual table which is minute based.
>
> I thought I would make a generated temp table (generate_series) and then
> join these values based on minue.
>
> 03:00 10
> 03:01 NULL
> 03:02 12
> 03:03 11
> 03:04 NULL
> 03:05 13
>
> I need a code to replace the value to the last value on NULL.
>
> 03:00 10
> 03:01 10 <
> 03:02 12
> 03:03 11
> 03:04 11 <
> 03:05 13
>

Unfortunately, as per
https://www.postgresql.org/docs/current/functions-window.html
"The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead,
lag, first_value, last_value, and nth_value. This is not implemented in
PostgreSQL: the behavior is always the same as the standard's default,
namely RESPECT NULLS".

So, I'd keep the incoming data as is, i.e. with no nulls in values, and
densify it with some generate_series magic:
select gmin as mmin, d.value
from
( select mmin, lead(mmin) over (order by mmin) nextmin, value from
test_table ) d,
generate_series(d.mmin, nextmin - interval'1 minute') gmin

(I assumed a time representation for mmin, but adapt the generate_series
call to whatever your representation is)
Best,
g

Re: AW: AW: Cast INTEGER to BIT confusion

[GF]

Erik,
The could be a possible solution, if you could play with search_path...
You could mirror all the original schema onto another one with views that
have the original table names.
For all tables that need some special treatment you can then write
instead-of triggers, while all other views are updatable.

E.g., let's say they gave you a schema like:
create schema original;
create table original.t(f1 bit);
create table original.u(f2 int);

You can mirror it in this way:
create schema mirror;
create view mirror.t as select f1::int from original.t;
create view mirror.u as select * from original.u;
create function mirror.ins_t() returns trigger as $$ begin insert into
original.t(f1) values((new.f1)::bit); return null; end; $$ language plpgsql;
create trigger ins_t instead of insert on mirror.t for each row execute
function mirror.ins_t();
-- And something like that for upd and del...

The user that you use to connect must be granted the rights on mirror views
and on original tables that need conversion. Its default search_path must
be altered to the mirror schema (if you are not already able to do so on
the connection string):
create user foo;
alter user foo set search_path to mirror;
grant usage on schema original, mirror to foo;
grant insert, update, delete on original.t, mirror.t, mirror.u to foo;  --
you need original.t but not original.u...

And now you can do:
psql -U foo -c "insert into t values(1)" -c "insert into u values(42)"

Best,
Giovanni

Re: AW: AW: Cast INTEGER to BIT confusion

[GF]

On Thu, 17 Aug 2023 at 19:09, GF  wrote:

>
> Erik,
> The could be a possible solution, if you could play with search_path...
>
>
  (sorry, Erik, obviously my message was intended to Markus)

Re: Getting specific partition from the partition name

[GF]

On Fri, 9 Aug 2024 at 06:20, Ron Johnson  wrote:

>
> What if the partitions aren't all rationally named?  There *must* be a
> pg_* table out there which contains the partition boundaries...
>
>
The pg_class column relpartbound contains an internal representation of the
partition boundary, when applicable.
You can decompile it into the canonical text format with pg_get_expr( expr
pg_node_tree, relation oid [, pretty boolean ] ) → text.
So:
create table t(x int primary key) partition by list(x);
create table u partition of t for values in (0,1);
create table v partition of t for values in (2,3,4,5,6,7,8,9);
select oid::regclass,pg_get_expr(relpartbound,oid) from pg_class where
relkind='r' and relispartition;
 oid |  pg_get_expr
-+
 u   | FOR VALUES IN (0, 1)
 v   | FOR VALUES IN (2, 3, 4, 5, 6, 7, 8, 9)
(2 rows)

Best,
Giovanni

Re: Logical Replication Delay

[GF]

Hi Ram,

 29 set 2024, 12:29 Ramakrishna m :

*We are planning to set up logical replication from a standby to another
> server. When the primary goes down, there is no issue as the standby
> becomes the primary and the logical slots are already present. However,
> when the standby goes down, these slots are not copied to the third node or
> the primary by Patroni. Is there an option available to handle this
> scenario? *
>

You could take a look at the pg_failover_slots extension (
https://www.enterprisedb.com/docs/pg_extensions/pg_failover_slots/), it is
aimed exactly at cloning the slot information to a standby.

Best,
giovanni

Re: Why is DELETE FROM vehicle_data WHERE NOW, > expires_at, failing in my stored function?

[GF]

Hi Alex,

On Sat, 18 Jan 2025 at 12:21, Alexander Farber 
wrote:

> [...]
>

> -- Run 2 smoke tests
> DO $$
> DECLARE
> test_result RECORD;
> BEGIN
> -- Test 1: store 15x4 records expiring in 5 seconds
> SELECT * INTO test_result FROM test_store_vehicle_data(15);
> IF test_result.count_true != 10 OR test_result.count_false != 5 THEN
> RAISE EXCEPTION 'Test 1 failed: expected 10 TRUE, 5 FALSE';
> END IF;
>
> -- Sleep for 10 seconds, so that all records in vehicle_data expire
> PERFORM pg_sleep(10);
>
> -- Test 2: store 15x4 records expiring in 5 seconds
> SELECT * INTO test_result FROM test_store_vehicle_data(15);
> IF test_result.count_true != 10 OR test_result.count_false != 5 THEN
> RAISE EXCEPTION 'Test 2 failed: expected 10 TRUE, 5 FALSE';
> END IF;
> END $$;
>
> -- Print all records in the vehicle_data table
> SELECT expires_at < NOW() AS expired, * FROM vehicle_data ORDER BY
> container_id;
>
> My problem is that the DELETE FROM vehicle_data WHERE NOW() > expires_at;
> statement in my store_vehicle_data() function does not see to delete
> anything.
>
> And then the SELECT expires_at < NOW() AS expired, * FROM vehicle_data
> ORDER BY container_id; prints the records in the table and yes, they are
> all expired there.
>
> I run the above SQL code (creating tables, creating functions, running
> smoke test) in a Dockerfile based on the official Postgres Dockerfile and
> the smoke test (the Test 2) just always fails.
>
> Then I connect using psql to my docker container and run the DELETE
> command at the psql prompt and voila - it works there as expected and
> deletes all the expired records.
>
>
The function now() returns the timestamp of the transaction start, so it
will be stable during all your "do $$ ...end; $$;" smoke test toplevel
block: i.e., it will return the same value across the two
test_store_vehicle_data() invocations, so the predicate "where NOW() >
expires_at" in store_vehicle_data() will return no rows. That's also why
running a second time the delete command it worked: you were running it in
another transaction after more than 10 seconds from the first one.
If you want to keep a stable semantics (i.e., still use now() in your
store_vehicle_data() function), then you should split your smoke test in
two distinct "do $$ end; $$;" invocations, one for the first call to
test_store_vehicle_data() with pg_sleep(), and one for the second call.
Otherwise you could use clock_timestamp() instead of now() inside
store_vehicle_data(), as this returns the actual current timestamp (maybe
storing it in a variable _x to be used in the "where _x > expires_at"
condition, to avoid the "sliding predicate" effect of "where
clock_timestamp() > expires_at").

Best,
giovanni