On 7/6/22 12:51, Matthias Apitz wrote:
El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió:
On 7/6/22 01:18, Matthias Apitz wrote:
[snip]
Ofc, each table has its own primary key(s), used for example for the
SELECT ctid, * FROM d01buch WHERE ...
As I said, we came to Postg
> On Jul 6, 2022, at 12:51, Matthias Apitz wrote:
> it is uniqu to identify a row in a table once
> known.
I think the point that we are trying to make here is that a ctid *isn't* that.
There is no guarantee, at all, at any level, that the ctid of a row will remain
stable, not even between
El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió:
> On 7/6/22 01:18, Matthias Apitz wrote:
> [snip]
> > Ofc, each table has its own primary key(s), used for example for the
> > SELECT ctid, * FROM d01buch WHERE ...
> >
> > As I said, we came to PostgreSQL from Sybase (and
On 7/6/22 01:18, Matthias Apitz wrote:
[snip]
Ofc, each table has its own primary key(s), used for example for the
SELECT ctid, * FROM d01buch WHERE ...
As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has
for each table a so called SYB_IDENTITY_COLUMN which is static for the
> Said this, we can end this thread. Re-think the data model is not an
> option.
Why not ?
- add a primary key to each table, say db01buch.pk
- rename tables, say db01buch -> db01buch__real_table
- add views, say db01buch over db1buch__real_table with "pk AS ctid"
(untest
On Wed, Jul 06, 2022 at 02:26:00PM +0200, Matthias Apitz wrote:
> El d??a Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert
> escribi??:
>
> > Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:
> >
[skipped]
> WHERE-clause of its interest ("WHERE d01status=4"), here a
El día miércoles, julio 06, 2022 a las 03:53:54p. m. +0200, Peter J. Holzer
escribió:
> On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote:
> > DB layer must LOCK the row for update. It does so using the CTID. Of
> > course there is a key in the row (d01gsi, the signature of the book),
> > but th
On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote:
> DB layer must LOCK the row for update. It does so using the CTID. Of
> course there is a key in the row (d01gsi, the signature of the book),
> but this is not uniqu and can't be used to lock exactly this row for
> update.
If it isn't unique it
On 2022-Jul-06, Matthias Apitz wrote:
> This gives in the DB layer a CURSOR of say 100.000 rows of the
> 3.000.000 in the table. Now the application fetches row by row and see
> if something should be done with the row. If so, the DB layer must
> LOCK the row for update. It does so using the CTID.
El día Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert escribió:
> Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:
>
> > > On first glance, it appears that you are using the ctid as a primary key
> > > for a row, and that's highly not-recommended. The ctid is neve
Am Wed, Jul 06, 2022 at 08:41:58AM +0200 schrieb Laurenz Albe:
> Using the primary key is the proper solution. To be safe from concurrent
> modifications, use a logic like in this pseudo-code:
>
> FOR b IN SELECT pk, other_columns FROM books WHERE condition
>UPDATE books SET ... WHERE pk = ..
Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz:
> > On first glance, it appears that you are using the ctid as a primary key
> > for a row, and that's highly not-recommended. The ctid is never intended
> > to be stable in the database, as you have discovered. There are really
On Wed, 2022-07-06 at 08:18 +0200, Matthias Apitz wrote:
> El día martes, julio 05, 2022 a las 10:44:23p. m. -0700, Christophe Pettus
> escribió:
>
> >
> >
> > > On Jul 5, 2022, at 22:35, Matthias Apitz wrote:
> > > Internally, in the DB layer, the read_where() builds the row list matching
> >
El día martes, julio 05, 2022 a las 10:44:23p. m. -0700, Christophe Pettus
escribió:
>
>
> > On Jul 5, 2022, at 22:35, Matthias Apitz wrote:
> > Internally, in the DB layer, the read_where() builds the row list matching
> > the WHERE clause as a SCROLLED CURSOR of
> >
> >SELECT ctid, * FR
Am 06.07.22 um 07:54 schrieb Andreas Kretschmer:
Am 06.07.22 um 07:44 schrieb Christophe Pettus:
On Jul 5, 2022, at 22:35, Matthias Apitz wrote:
Internally, in the DB layer, the read_where() builds the row list
matching
the WHERE clause as a SCROLLED CURSOR of
SELECT ctid, * FROM
Am 06.07.22 um 07:44 schrieb Christophe Pettus:
On Jul 5, 2022, at 22:35, Matthias Apitz wrote:
Internally, in the DB layer, the read_where() builds the row list matching
the WHERE clause as a SCROLLED CURSOR of
SELECT ctid, * FROM d01buch WHERE ...
and each fetch() delivers the next
> On Jul 5, 2022, at 22:35, Matthias Apitz wrote:
> Internally, in the DB layer, the read_where() builds the row list matching
> the WHERE clause as a SCROLLED CURSOR of
>
>SELECT ctid, * FROM d01buch WHERE ...
>
> and each fetch() delivers the next row from this cursor. The functions
> s
El día martes, julio 05, 2022 a las 06:19:18p. m. +0200, Laurenz Albe escribió:
> > Hi Laurenz, ist there any way to keep/freeze such tuples until the run
> > of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
> > even a code change for this? We compile on Linux from the sources
On Tue, 2022-07-05 at 17:52 +0200, Matthias Apitz wrote:
> El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe
> escribió:
>
> > > > Another explanation could be that the HOT chain was pruned while you
> > > > were away.
> > >
> > > I've read now about HOT and understand that
El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe escribió:
> > > Another explanation could be that the HOT chain was pruned while you were
> > > away.
> >
> > I've read now about HOT and understand that autovacuum will prune the
> > HOT chain. But also a simple SELECT seems
On Tue, 2022-07-05 at 12:22 +0200, Matthias Apitz wrote:
> El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> > On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > > We're using the SQL function currtid2() to get the new CTID of a row
> > > when this was UPDATEd.
El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> >
> > Investigating cases of failing updates, it turns out t
El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> >
> > Investigating cases of failing updates, it turns out t
On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> We're using the SQL function currtid2() to get the new CTID of a row
> when this was UPDATEd.
>
> Investigating cases of failing updates, it turns out that the old CTID
> has only a limited lifetime; one can check this with SQL:
>
> sisis
24 matches
Mail list logo
