Re: versions table has two rows

On Mon, Sep 16, 2024, at 8:55 PM, Phil Stracchino wrote:
> On 9/16/24 20:34, Dan Langille wrote:
>> On Mon, Sep 16, 2024, at 5:46 PM, Phil Stracchino wrote:
>>> On 9/16/24 15:17, Dan Langille wrote:
>>>
>>> delete from version order by version limit 1;
>> 
>> bacula=*# delete from version order by version limit 1;
>> ERROR:  syntax error at or near "order"
>> LINE 1: delete from version order by version limit 1;
>
> Doh, my bad.  Forgot you can't use ORDER BY in a DELETE FROM.  (Which 
> means you have to use shenanigans when you want to do that.)

I found a solution after some searching: use ctid

From https://www.postgresql.org/docs/current/ddl-system-columns.html#DDL-SYSTEM-COLUMNS-CTID

"The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. A primary key should be used to identify logical rows."

Before PostgreSQL-12, we had access to the oid column, which was my first resort when trying this.

[19:10 pg03 dvl ~] % psql bacula            
psql (16.4)
Type "help" for help.

bacula=# select ctid, * from version;
  ctid  | versionid 
--------+-----------
 (0,21) |      1026
 (0,22) |      1026
(2 rows)

bacula=# begin;
BEGIN
bacula=*# delete from version where ctid = '(0,22)';
DELETE 1
bacula=*# commit;
COMMIT
bacula=# 

-- 
  Dan Langille
  [email protected]