Re: How to implement expiration in PostgreSQL?

On 01/04/2021 02:51, Glen Huang wrote: > Hi, > > I guess this question has been asked a million times, but all solutions I can > find online don’t really work well for my case. I’ll list them here and hope > someone can shed some light. > > My use case is to implement joining clubs that require e

Re: accessing cross-schema materialized views

On 31/03/2021 23:42, Tom Lane wrote: > The owner of the schema2.usingview is the one who must have > privilege to read the underlying schema1.matview. Our > permissions messages are, I fear, frequently not very good > about saying whose privileges were checked. > > regards, tom lane That nailed

concurrent creation of sequences

Hi, I have a use case where I need to create sequences concurrently. I use sequences instead of a table with counters to keep lock contention for incrementing the id low. The creation of new sequences rarely occurs during normal application operation. I check via information_schema.sequences

Re: Looking for some help with HA / Log Log-Shipping

Hi, Postgresql 11 already changed the position of replication parameters, now all hosted in the main postgresql.conf file. So on the standby node, instead of a recovery.conf file, just an empty STANDBY.SIGNAL file is needed in the $PGDATA directory to start Postgresql as a standby replica on that n

Re: How to implement expiration in PostgreSQL?

> Possibly keep your count of members updated via a trigger? But how to evoke the trigger when the count of members should be updated by a timeout, i.e., the person’s pay is due?

Re: How to implement expiration in PostgreSQL?

On 01/04/2021 14:28, Glen Huang wrote: >> Possibly keep your count of members updated via a trigger? > But how to evoke the trigger when the count of members should be updated by a > timeout, i.e., the person’s pay is due? I'd run a cron job that triggers a function call which would make the nec

Re: How to implement expiration in PostgreSQL?

Good suggestion, thanks. I ended up running the cron jobs in my app, one for each newly joined member, to get the smallest granularity possible. > On Apr 1, 2021, at 9:43 PM, Tim Clarke wrote: > > On 01/04/2021 14:28, Glen Huang wrote: >>> Possibly keep your count of members updated via a trig

Re: How to implement expiration in PostgreSQL?

On 01/04/2021 14:47, Glen Huang wrote: > Good suggestion, thanks. > > I ended up running the cron jobs in my app, one for each newly joined member, > to get the smallest granularity possible. (This list bottom-posts by convention) I'd say that was onerous and you could get the same effect with

Re: How to implement expiration in PostgreSQL?

> I'd say that was onerous and you could get the same effect with a well-crafted query that targetted only those that might possibly expire. I wish one cron job could rule them all, but since a person can decide to join at any time, her expiration (e.g., after 1 hour) can also happen at any time.

Re: How to implement expiration in PostgreSQL?

On 01/04/2021 15:23, Glen Huang wrote: > I wish one cron job could rule them all, but since a person can decide to > join at any time, her expiration (e.g., after 1 hour) can also happen at any > time. So one cron job won’t cut it if a member’s expiration has to bee > accurate. One cron job r

Re: How to implement expiration in PostgreSQL?

Hmm, maybe letting people linger on for a couple more minutes isn’t unacceptable. And it simplifies the code a lot. Good idea, thanks. > On Apr 1, 2021, at 10:31 PM, Tim Clarke wrote: > > > On 01/04/2021 15:23, Glen Huang wrote: >> I wish one cron job could rule them all, but since a person c

Is replacing transactions with CTE a good idea?

Hi all, From application’s standpoint, it seems using CTE saves a lot work. You no longer need to parse values out only to pass them back in, and only one round-trip to the db server. If I’m not wrong, CTE is equivalent to serializable transactions? So I guess the downsize is that quarries can

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: > If I decide to replace all my transaction code with CTE, will I shoot > myself in the foot down the road? > I do this all the time and makes code way cleaner. It's very straightforward with inserts queries. When you deal with updates/deletes

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: > Hi all, > > From application’s standpoint, it seems using CTE saves a lot work. You no > longer need to parse values out only to pass them back in, and only one > round-trip to the db server. > > If I’m not wrong, CTE is equivalent to serializable

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time and makes code way cleaner.   It's very

Re: Is replacing transactions with CTE a good idea?

> When you deal with updates/deletes, things can be trickier Care to expand why they are tricker? I presume they run the risk of being referenced more than once? > On Apr 1, 2021, at 10:58 PM, Brian Dunavant wrote: > >  >> On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: >> If I decide to r

Re: Is replacing transactions with CTE a good idea?

No, but are they equivalent to serializable transactions? > On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: > >  > > > >> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >> Hi all, >> >> From application’s standpoint, it seems using CTE saves a lot work. You no >> longer need to parse valu

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: > No, but are they equivalent to serializable transactions? > No, they are not. Dave Cramer www.postgres.rocks > > On Apr 1, 2021, at 11:04 PM, Dave Cramer > wrote: > >  > > > > On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: > >> Hi all, >> >

Re: Is replacing transactions with CTE a good idea?

Sorry, my mistake. I misunderstood serializable. Are queries in a CTE equivalent to those in a repeatable read transaction? > On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: > >  > > >> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: >> No, but are they equivalent to serializable transaction

Re: Is replacing transactions with CTE a good idea?

CTE's don't change the isolation level. I'm not sure what you are getting at here ? Dave Cramer www.postgres.rocks On Thu, 1 Apr 2021 at 11:20, Glen Huang wrote: > Sorry, my mistake. I misunderstood serializable. Are queries in a CTE > equivalent to those in a repeatable read transaction? > >

Re: Is replacing transactions with CTE a good idea?

Ah, I see what you mean. You still have to wrap a CTE inside a transaction to specify the isolation level? By default, queries in a CTE run with the read committed isolation level? > On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: > >  > > >> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote:

Re: Is replacing transactions with CTE a good idea?

I had the impression that since they are chained together, somehow they run “tighter” 😂. Thanks for pointing out that mistake. > On Apr 1, 2021, at 11:25 PM, Dave Cramer wrote: > >  > CTE's don't change the isolation level. I'm not sure what you are getting at > here ? > > Dave Cramer > www

storage in mem

too my understanding, postgres is now architected so that engine and storage are ""splited"" and so one may think that storage code different that the traditional postgres thing is possible. I have heard of oracle like redo log things, ..etc I wonder if a memory thing is on the way, somewhere ?

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:06 AM Glen Huang wrote: > Care to expand why they are tricker? I presume they run the risk of being > referenced more than once? > There are lots of gotchas. It's also been a few years since I dug deep into this, so some of this may have changed in more recent versions.

Re: How to implement expiration in PostgreSQL?

On Thu, Apr 1, 2021 at 7:23 AM Glen Huang wrote: > > I'd say that was onerous and you could get the same effect with a > well-crafted query that targetted only those that might possibly expire. > > I wish one cron job could rule them all, but since a person can decide to > join at any time, her e

Slick way to update multiple tables.

I SELECT items from three tables. It seems that the only way to put updated fields into their proper table is to query *each* field, then UPDATE *each* table. Am I missing something here? Is there a way to do this automatically? -- GnuPG/PGP key: 0xDD4276BA +-

Re: Slick way to update multiple tables.

You can have an updatable view.

Re: Slick way to update multiple tables.

On Thu, Apr 1, 2021 at 12:43 PM Michael Lewis wrote: > You can have an updatable view. > *Show-stopper?* The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view. Or multiple VIEWs? I've never done VIEWs... -- GnuPG/PGP ke

Re: Slick way to update multiple tables.

Joins are allowed though.

Re: questions about wraparound

Hi Luca, On Mon, 22 Mar 2021 08:56:46 +0100 Luca Ferrari wrote: > I can confirm that freezing a template database is done by means of setting > it age to zero. [...] > and here it is the situation after a restart: > > testdb=> select datname, age( datfrozenxid ) from pg_database; > datname

Re: Slick way to update multiple tables.

postgresql.org/docs/current/sql-createview.html My apologies. It seems INSTEAD OF triggers are required to implement updates across multiple tables. I thought not if all were simple joins. My mistake.

Re: Slick way to update multiple tables.

On 4/1/21 11:54 AM, Michael Lewis wrote: postgresql.org/docs/current/sql-createview.html My apologies. It seems INSTEAD OF triggers are required to implement updates across multiple tables. I thought not if all were simple joins. My mis

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > CTE's don't change the isolation level. I'm not sure what you are getting at > here ? I think what he/she means here is that all queries in a CTE use a single snapshot, meaning you don't see changes by commits that happen between queri

Re: Upgrading from 11 to 13

On Tue, Mar 30, 2021 at 08:30:00AM -0700, David G. Johnston wrote: > On Tue, Mar 30, 2021 at 8:25 AM Daniel Westermann (DWE) < > daniel.westerm...@dbi-services.com> wrote: > The best place would be the release notes, I guess. Right at the beginning > here: > > Release notes are probably a

Re: Upgrading from 11 to 13

On 4/1/21 6:06 PM, Bruce Momjian wrote: An explicit sentence stating that major upgrades can skip major versions is >> needed.  The document is written assuming the reading knows this, and just >> makes a few minor notes on the topic: >> e.g., "If you are upgrading across several major versions, be

Re: Upgrading from 11 to 13

I seem to recall that going from 11 to 12, a certain configuration file was removed and the keys are now expected to be set in the regular configuration file? The logic being there should only ever be 1 configuration file. I can't find it, but at the same time I don't recall what it's called. I be

Re: Upgrading from 11 to 13

On 2021-Apr-02, Koen De Groote wrote: > I seem to recall that going from 11 to 12, a certain configuration file was > removed and the keys are now expected to be set in the regular > configuration file? The logic being there should only ever be 1 > configuration file. > > I can't find it, but at

Re: Upgrading from 11 to 13

On Thu, Apr 1, 2021 at 07:26:14PM -0400, Jonathan Katz wrote: > On 4/1/21 6:06 PM, Bruce Momjian wrote: > An explicit sentence stating that major upgrades can skip major versions is > >> needed.  The document is written assuming the reading knows this, and just > >> makes a few minor notes on the

Re: Upgrading from 11 to 13

Here it is with descriptions: Hepatitis A Dates on file: 09/01/2005, 01/27/2005 IPV (Polio) Dates on file: 09/01/2005 Influenza Vaccine Quadrivalent 3 Yr And Older Dates on file: 08/25/2020, 10/19/2015 Influenza V

Re: Upgrading from 11 to 13

On Thu, Apr 1, 2021 at 09:55:28PM -0400, Bruce Momjian wrote: > > Here it is with descriptions: Sorry, please ignore. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusio

Re: Primary keys and composite unique keys(basic question)

On Wed, Mar 31, 2021 at 3:36 AM Mohan Radhakrishnan wrote: > > Hello, > We have UUIDs in our tables which are primary keys. But in some > cases > we also identify a composite unique key apart from the primary key. > > My assumption is that there should be a unique key index created b

Re: Primary keys and composite unique keys(basic question)

On 4/1/21 8:28 PM, Merlin Moncure wrote: This is one of the great debates in computer science and it is not settled. There are various tradeoffs around using a composite key derived from the data (aka natural key) vs generated identifiers. It's a complex topic with many facets: performance, org

Re: Primary keys and composite unique keys(basic question)

I used uuid4 for customer ids because i needed to interface with payment providers. Is that wrong? All other places except transaction ids, i have used serial ints On Fri 2 Apr, 2021, 8:56 AM Rob Sargent, wrote: > On 4/1/21 8:28 PM, Merlin Moncure wrote: > > > > This is one of the great debates