Client Computers

[Jason L. Amerson]

Do my Windows 10 computers that will be clients, need PostgreSQL installed
to connect remotely to my server or is pgAdmin or some other client all I
need?

 

Jason L. Amerson

Re: Finding out about the dates of table modification

[Alban Hertroys]

> On 23 Nov 2019, at 3:24, Martin Mueller  
> wrote:
> 
> I've moved happily from MySQL to Postgres but miss one really good feature of 
> MYSQL: the table of tables that let you use SQL queries to find out metadata 
> about your table. Thus looking at the table of tables and sorting it by last 
> change, lets you quickly look at the most recently modified table. Which is 
> really useful if you have a bad memory, as I do, and can't remember the name 
> of a particular table that I worked on last Wednesday. 
> 
> Are those capabilities hidden somewhere in Postgres?  There isn't an obvious 
> section in the documentation. At least I can't find it. 

AFAIK, there’s nothing like that built-in, but it’s not impossible to deduce.

You could start with getting a list of files in $PG_DATA/base that were 
modified in that period (provided you have sufficient privileges on that 
directory):

find base/ -mtime -2 -type f -print

For figuring out to what tables these files belong [1]:

pg_filenode_relation(0, );

and:

pg_relation_filepath();

For example, I did:
# create table creation_date(test text);

[/home/postgres/10/data]$ find base/ -mtime -2 -type f -print   
  
base/16403/2608
base/16403/29784
base/16403/2659
base/16403/29789
base/16403/2678
base/16403/29787
base/16403/2662
base/16403/2703
base/16403/2679
base/16403/2673
base/16403/2658
base/16403/1249
base/16403/2610
base/16403/2704
base/16403/2674
base/16403/3455
base/16403/2663
base/16403/1247
base/16403/1259

The lower numbers are probably core tables, such as pg_depend:
# SELECT pg_filenode_relation(0, 2608); -- 0 being the default table-space
 pg_filenode_relation 
--
 pg_depend
(1 row)

But!:
# SELECT pg_filenode_relation(0, 29784);
 pg_filenode_relation 
--
 creation_date
(1 row)


And indeed:
# select pg_relation_filepath('creation_date');   
 pg_relation_filepath 
--
 base/16403/29784
(1 row)


I was looking for the inverse function pg_filepath_relation(), but 
that does not appear to exist; That would have been useful in combination with 
file listings like those from `find`.

Mind that larger tables consist of multiple files. I’m sure this would become a 
head-ache quick on a larger database. Having an actual creation-date of a file 
would be nice too, but that doesn’t necessarily mean much when growing tables 
create extra files too.

Apparently, someone already turned the process into a number of queries[2]. As 
they mention though, it’s not 100% reliable though, as there are operations 
that recreate table files, such as CLUSTER.

Then again, if you’re just looking for the table you created last Wednesday, 
that’s probably not a major concern.


Another option is to add a DDL Event trigger on create table statements and log 
that to some table[3].

Regards,
Alban Hertroys

[1]: https://www.2ndquadrant.com/en/blog/postgresql-filename-to-table/
[2]: 
https://stackoverflow.com/questions/18849756/automatically-drop-tables-and-indexes-older-than-90-days/18852752#18852752
[3]: https://www.postgresql.org/docs/current/event-triggers.html

--
There is always an exception to always.

Re: Client Computers

[Daniel Baktiar]

Hi Jason,

AFAIK, you just need the pgadmin installed.

Regards,
Daniel

On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson 
wrote:

> Do my Windows 10 computers that will be clients, need PostgreSQL installed
> to connect remotely to my server or is pgAdmin or some other client all I
> need?
>
>
>
> Jason L. Amerson
>

RE: Client Computers

[Jason L. Amerson]

Thanks

 

Jason L. Amerson

 

 

From: Daniel Baktiar  
Sent: Saturday, November 23, 2019 07:15 AM
To: Jason L. Amerson 
Cc: PostgreSQL 
Subject: Re: Client Computers

 

Hi Jason,

 

AFAIK, you just need the pgadmin installed.

 

Regards,

Daniel

 

On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson mailto:drja...@alphagenius.org> > wrote:



Do my Windows 10 computers that will be clients, need PostgreSQL installed to 
connect remotely to my server or is pgAdmin or some other client all I need?

 

Jason L. Amerson

Re: And I thought I had this solved.

[stan]

On Fri, Nov 22, 2019 at 04:06:14PM -0800, Adrian Klaver wrote:
> On 11/22/19 3:52 PM, stan wrote:
> > A while back I ran into problems caused by  security fix related to the
> > search path. I wound up adding a line to. for instance, this function:
> > 
> > REATE FUNCTION
> > work_hours
> > (
> >  start_date date,
> > end_date date
> > )
> > RETURNS decimal(10,4) stable
> > language sql as $$
> > 
> > /* workaround for secuirty "feature" */
> > SET search_path TO ica, "user" , public;
> > 
> > SELECT
> > sum(case when
> > extract(isodow from d)
> > between 1 and 5 then
> > 8.0 else
> > +0.0 end)
> > from
> > 
> > generate_series($1,
> > $2, interval
> > '24 hours') d;
> > 
> > $$;
> > 
> > And walked away happy, or so I thought. Now I just got this error:
> > 
> > [local] stan@stan=# select * from ttl_avail_hours_by_project_and_employee ;
> > ERROR:  SET is not allowed in a non-volatile function
> > CONTEXT:  SQL function "work_hours" during startup
> > 
> > How can I solve this issue?
> 
> 
> I thought I was missing something. Third option. As example:
> 
> https://www.postgresql.org/docs/11/sql-createfunction.html
> 
> Writing
> SECURITY DEFINER
> Functions Safely
> 
> 
> ...
> 
> $$  LANGUAGE plpgsql
> SECURITY DEFINER
> -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
> SET search_path = admin, pg_temp;
> 
> Put the SET outside the function body.

OH, that seems the cleanest way to do this.

Thanks.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Client Computers

[Tim Clarke]

The ODBC drivers are generally very useful.

Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

On 23/11/2019 12:23, Jason L. Amerson wrote:
>
> Thanks
>
> Jason L. Amerson
>
> *From:* Daniel Baktiar 
> *Sent:* Saturday, November 23, 2019 07:15 AM
> *To:* Jason L. Amerson 
> *Cc:* PostgreSQL 
> *Subject:* Re: Client Computers
>
> Hi Jason,
>
> AFAIK, you just need the pgadmin installed.
>
> Regards,
>
> Daniel
>
> On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson
> mailto:drja...@alphagenius.org>> wrote:
>
> Do my Windows 10 computers that will be clients, need PostgreSQL
> installed to connect remotely to my server or is pgAdmin or some
> other client all I need?
>
> Jason L. Amerson
>


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: Client Computers

[Ron]

On 11/23/19 4:25 AM, Jason L. Amerson wrote:


Do my Windows 10 computers that will be clients, need PostgreSQL installed 
to connect remotely to my server or is pgAdmin or some other client all I 
need?




They only need pgAdmin installed if they're going to actually run pgAdmin.

If they're "just" going to run some specific program, then they only need 
that program and the relevant driver (JDBC, ODBC, etc).


--
Angular momentum makes the world go 'round.

RE: Client Computers

[Jason L. Amerson]

Thanks Ron.

 

Jason L. Amerson

 

 

From: Ron  
Sent: Saturday, November 23, 2019 09:38 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Client Computers

 

On 11/23/19 4:25 AM, Jason L. Amerson wrote:





Do my Windows 10 computers that will be clients, need PostgreSQL installed to 
connect remotely to my server or is pgAdmin or some other client all I need?


They only need pgAdmin installed if they're going to actually run pgAdmin.

If they're "just" going to run some specific program, then they only need that 
program and the relevant driver (JDBC, ODBC, etc).

-- 
Angular momentum makes the world go 'round.

RE: Client Computers

[Jason L. Amerson]

Thanks Tim.

Jason L. Amerson


-Original Message-
From: Tim Clarke  
Sent: Saturday, November 23, 2019 09:32 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Client Computers

The ODBC drivers are generally very useful.

Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

On 23/11/2019 12:23, Jason L. Amerson wrote:
>
> Thanks
>
> Jason L. Amerson
>
> *From:* Daniel Baktiar 
> *Sent:* Saturday, November 23, 2019 07:15 AM
> *To:* Jason L. Amerson 
> *Cc:* PostgreSQL 
> *Subject:* Re: Client Computers
>
> Hi Jason,
>
> AFAIK, you just need the pgadmin installed.
>
> Regards,
>
> Daniel
>
> On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson 
> mailto:drja...@alphagenius.org>> wrote:
>
> Do my Windows 10 computers that will be clients, need PostgreSQL
> installed to connect remotely to my server or is pgAdmin or some
> other client all I need?
>
> Jason L. Amerson
>


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: How to drop all tokens that a snowball dictionary cannot stem?

[Jeff Janes]

On Fri, Nov 22, 2019 at 8:02 AM Christoph Gößmann  wrote:

> Hi everybody,
>
> I am trying to get all the lexemes for a text using to_tsvector(). But I
> want only words that english_stem -- the integrated snowball dictionary --
> is able to handle to show up in the final tsvector. Since snowball
> dictionaries only remove stop words, but keep the words that they cannot
> stem, I don't see an easy option to do this. Do you have any ideas?
>
> I went ahead with creating a new configuration:
>
> -- add new configuration english_led
> CREATE TEXT SEARCH CONFIGURATION public.english_led (COPY =
> pg_catalog.english);
>
> -- dropping any words that contain numbers already in the parser
> ALTER TEXT SEARCH CONFIGURATION english_led
> DROP MAPPING FOR numword;
>
> EXAMPLE:
>
> SELECT * from to_tsvector('english_led','A test sentence with ui44 \tt
> somejnk words');
>to_tsvector
> --
>  'sentenc':3 'somejnk':6 'test':2 'tt':5 'word':7
>
> In this tsvector, I would like 'somejnk' and 'tt' not to be included.
>

I don't think the question is well defined.  It will happily stem
'somejnking' to ' somejnk', doesn't that mean that it **can** handle it?
The fact that 'somejnk' itself wasn't altered during stemming doesn't mean
it wasn't handled, just like 'test' wasn't altered during stemming.

Cheers,

Jeff

Re: How to drop all tokens that a snowball dictionary cannot stem?

[Christoph Gößmann]

Hi Jeff,

You're right about that point. Let me redefine. I would like to drop all tokens 
which neither are the stemmed or unstemmed version of a known word. Would there 
be the possibility of putting a wordlist as a filter ahead of the stemming? Or 
do you know about a good English lexeme list that could be used to filter after 
stemming?

Thanks,
Christoph 

> On 23. Nov 2019, at 16:27, Jeff Janes  wrote:
> 
> On Fri, Nov 22, 2019 at 8:02 AM Christoph Gößmann  > wrote:
> Hi everybody,
> 
> I am trying to get all the lexemes for a text using to_tsvector(). But I want 
> only words that english_stem -- the integrated snowball dictionary -- is able 
> to handle to show up in the final tsvector. Since snowball dictionaries only 
> remove stop words, but keep the words that they cannot stem, I don't see an 
> easy option to do this. Do you have any ideas?
> 
> I went ahead with creating a new configuration:
> 
> -- add new configuration english_led
> CREATE TEXT SEARCH CONFIGURATION public.english_led (COPY = 
> pg_catalog.english);
> 
> -- dropping any words that contain numbers already in the parser
> ALTER TEXT SEARCH CONFIGURATION english_led
> DROP MAPPING FOR numword;
> 
> EXAMPLE:
> 
> SELECT * from to_tsvector('english_led','A test sentence with ui44 \tt 
> somejnk words');
>to_tsvector
> --
>  'sentenc':3 'somejnk':6 'test':2 'tt':5 'word':7
> 
> In this tsvector, I would like 'somejnk' and 'tt' not to be included.
> 
> I don't think the question is well defined.  It will happily stem 
> 'somejnking' to ' somejnk', doesn't that mean that it **can** handle it?  The 
> fact that 'somejnk' itself wasn't altered during stemming doesn't mean it 
> wasn't handled, just like 'test' wasn't altered during stemming.
> 
> Cheers,
> 
> Jeff

Re: Constants in the foreighn key constraints

[Adrian Klaver]

On 11/22/19 11:36 PM, aleksey ksenzov wrote:

Please reply to list also.
Ccing list.


We're already on 12.

While I understand I can do everything with triggers/functions, for me 
it looks like a good idea to have possibility to use constants in 
constraints, so it would be very nice if postgres community could add 
this functionality in the nearest releases.

Regards,
Aliaksei.


On Fri, Nov 22, 2019 at 4:25 PM Adrian Klaver > wrote:


On 11/22/19 6:32 AM, aleksey ksenzov wrote:
 > Hi team.
 > Latest time we faced several issues which wouldn't arise provided we
 > have possibility to use constants in foreign key constraints.
 > brief example where it would be helpful:
 >
 > table_a
 > ( id uuid,
 > parent_id uuid,
 > is_deleted boolean
 > )
 > having possibility of FK (parent_id, false) to (id, is_deleted)
 > would disallow setting parent record deleted flag to true if they
have
 > children, or insert record with is_deleted = true parent.

Postgres version?

Look at triggers, in particular CONSTRAINT triggers:

https://www.postgresql.org/docs/12/sql-createtrigger.html

 >
 > Regards,
 > Aliaksei.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

RE: Remote Connection Help

[Jason L. Amerson]

I removed both version of PostgreSQL from Ubuntu. I had to delete some files 
manually. I used the repository to install PostgreSQL and then I configured it 
for remote access and now everything works great! I can log in from my Windows 
computers using pgAdmin. Thank you to you and everyone else who had the 
patience to work with me. I appreciate all of your help. I just have one last 
question. I see many tutorials on the Internet showing you how to setup 
iptables with PostgreSQL. Should I do this or not? I just wanted to ask before 
I did it and screwed something up and it would not have made that much 
difference.

Thanks again to everyone,

Jason L. Amerson



-Original Message-
From: Adrian Klaver  
Sent: Friday, November 22, 2019 01:11 PM
To: Jason L. Amerson 
Cc: 'PostgreSQL' 
Subject: Re: Remote Connection Help


On 11/22/19 8:27 AM, Jason L. Amerson wrote:
> When I run pg_lsclusters, I get the following:
> 
> Ver Cluster Port Status OwnerData directory
> 9.4 main5432 down   postgres /var/lib/postgresql/9.4/main
> 
> Log file
> /var/log/postgresql/postgresql-9.4-main.log
> 
> When I run select version();, I get the following:
> 
>   version
>  
> --
> 
> PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
> 7.4.0-1ubuntu1~
> 18.04.1) 7.4.0, 64-bit
> (1 row)

Alright so you are running the compiled version and you have a package version 
which is not running.

> 
> I logged into root and tried to run select, and now I cannot log into 
> the server under root. What the hell! I am so frustrated! This should 
> not be so difficult. Obviously something is very screwed up with two 
> servers. I

I should have been more specific, I meant as database superuser(postgres).

would like to properly remove the repository version and all files and then 
uninstall the install I did from source code and get rid of any other files 
with that so that I can just start over. Will you please help me do this the 
best way? Will I have to manually remove some of the files associated with the 
two installs? I really appreciate how great everyone has been in helping me. I 
just do not want to waste any more of our time when I can just get rid of 
everything and start over.

Starting over sounds like a good idea. To that end:

1) Do you have data on the running Postgres server you care about?
If so use pg_dump to backup it up.

2) I would stick with the package system instead of compiling from source.

3) Uninstall the current package version(9.4).

4) Assuming you did the pg_dump from 1) or don't care about the data, stop the 
compiled version. Then delete the /usr/local/pgsql/ directory and clean up the 
/etc/init.d/. If you want to be extra safe you could copy the previous to a 
backup location.

5) Decide where you want to get your packages from. I would suggest using the 
Postgres community repo:

https://www.postgresql.org/download/linux/ubuntu/
https://wiki.postgresql.org/wiki/Apt

If you are not already then point at the above repo.

6) Follow instructions at link in 5) to install Postgres.
Substitute 12 for 11 if you want 12. FYI just specifying postgresql (no
-version) will get you the latest.

7) Assuming installing 12 then go to:

/etc/postgresql/12/main

to make the necessary changes to postgresql.conf and pg_hba.conf.


8) For using the Debian/Ubuntu Postgres cluster management take a look at:

https://wiki.debian.org/PostgreSql

> 
> Also, do my Windows 10 computers that will be clients, need PostgreSQL 
> installed in order to connect remotely to my server or is pgAdmin or some 
> other client all I need?
> 
> Jason L. Amerson
> 
> 
> -Original Message-
> From: Adrian Klaver 
> Sent: Friday, November 22, 2019 09:56 AM
> To: Jason L. Amerson 
> Cc: 'PostgreSQL' 
> Subject: Re: Remote Connection Help
> 
> On 11/22/19 5:40 AM, Jason L. Amerson wrote:
>> Adrian,
>>
>> I originally did install PostgreSQL 12 from the repository. Then I 
>> removed it and decided to do it from source. I do have two 
>> postgresql.conf files and two pg_hba.conf files in
> 
> Well from your previous post "/etc/postgresql/9.4/main." That would indicate 
> there is also a 9.4 package installed or at least its conf files. At the 
> command line do:
> 
> pg_lsclusters
> 
> two different locations. I guess I need to know which one to keep. When I 
> enter SHOW config_file;, I get the location 
> /usr/local/pgsql/data/postgresql.conf. There is a pg_hba.conf file in there 
> too. Anyways, the version that I installed from source is version 12. The 
> setting in postgresql.conf is listen_addresses = ‘*’ and the port is 5432. 
> listen_addresses is uncommented but port is commented out.
> 
> What do you see if in psql you do?:
> 
> select version();
> 
> In your screenshot for pg_settings the value for listen_addresses is 
> 'localhost' and the source is default. That would indi

Re: Constants in the foreighn key constraints

[Tom Lane]

Adrian Klaver  writes:
> Please reply to list also.
> Ccing list.

> On 11/22/19 11:36 PM, aleksey ksenzov wrote:
>> While I understand I can do everything with triggers/functions, for me 
>> it looks like a good idea to have possibility to use constants in 
>> constraints, so it would be very nice if postgres community could add 
>> this functionality in the nearest releases.

It seems quite unlikely to me that we'd add such a thing.  It'd be
a weird wart on the foreign-key feature.  Notable problems:

* How would it interact with referential actions, notably
ON UPDATE CASCADE, ON UPDATE/DELETE SET NULL, ON UPDATE/DELETE SET DEFAULT?
I guess you could disallow those options for such a foreign key,
but anytime you have a feature that's that non-orthogonal with
existing ones, you have to ask yourself if you've designed it right.

* Such FKs couldn't be displayed in the information_schema views,
at least not without violating the letter and spirit of the SQL spec.
We already have some cases of constraints that can't be shown in
information_schema, but that's not the sort of wart I want more of.

BTW, it seems to me that you can get the same behavior with existing
features: make a regular multicolumn foreign key constraint, and then
add a CHECK constraint restricting what value one of the referencing
columns can have.  Yeah, this requires useless storage of a column
that will only ever have one value.  I think that's an okay limitation
for a niche use-case.  It also generalizes more easily to cases where
there's more than exactly one allowed value for a referencing column.

regards, tom lane

Re: Remote Connection Help

[Tom Lane]

"Jason L. Amerson"  writes:
> I removed both version of PostgreSQL from Ubuntu. I had to delete some
> files manually. I used the repository to install PostgreSQL and then I
> configured it for remote access and now everything works great! I can
> log in from my Windows computers using pgAdmin. Thank you to you and
> everyone else who had the patience to work with me. I appreciate all of
> your help. I just have one last question. I see many tutorials on the
> Internet showing you how to setup iptables with PostgreSQL. Should I do
> this or not?

Usually the reason for messing with iptables is either

1. You have to, because your machine's default firewall rules aren't
letting in connections to Postgres.  You evidently haven't got that
problem.

2. You want to block off access to the database server from addresses
you don't trust.  Configuring pg_hba.conf is typically a sufficient
answer for that; but you might want an iptables filter too, either
because you're a belt-and-suspenders-too kind of guy, or because you're
afraid of DDoS storms with more connection requests/sec than Postgres
can reject easily.  I can't say whether either of those apply to you;
but I would note that the DDoS scenario only applies if your server
is reachable from the net-at-large, which is seldom a good idea in
the first place.

regards, tom lane

Re: How to drop all tokens that a snowball dictionary cannot stem?

[Jeff Janes]

On Sat, Nov 23, 2019 at 10:42 AM Christoph Gößmann 
wrote:

> Hi Jeff,
>
> You're right about that point. Let me redefine. I would like to drop all
> tokens which neither are the stemmed or unstemmed version of a known word.
> Would there be the possibility of putting a wordlist as a filter ahead of
> the stemming? Or do you know about a good English lexeme list that could be
> used to filter after stemming?
>

I think what you describe is the opposite of what snowball was designed to
do.  You want an ispell-based dictionary instead.

PostgreSQL doesn't ship with real ispell dictionaries, so you have to
retrieve the files yourself and install them into $SHAREDIR/tsearch_data as
described in the docs for
https://www.postgresql.org/docs/12/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY

Cheers,

Jeff

Trouble incrementing a column

[Blake McBride]

Greetings,

I am using PostgreSQL 10.10.  I am having trouble incrementing a column for
reasons I can't see.  It's probably some basic SQL thing.  Your help is
appreciated.

create table my_table (
listid char(36) not null,
seq smallint not null,
item varchar(4096),
primary key (listid, seq)
);

insert into my_table (listid, seq) values ('abc', 1);
insert into my_table (listid, seq) values ('abc', 2);

-- the following works some of the time
update my_table set seq=seq+1;

-- the following doe not work for reasons I do not know
update my_table set seq=seq+1 where listid='abc';

What I get is a duplicate primary key.  I wouldn't think I'd get that
because I'd think the whole thing is done in a transaction so that
duplicate checks wouldn't be done till the end (essentially).

Is there a clean way to do this?

Thanks!

Blake McBride

Re: Trouble incrementing a column

[Ron]

On 11/23/19 3:28 PM, Blake McBride wrote:

Greetings,

I am using PostgreSQL 10.10.  I am having trouble incrementing a column 
for reasons I can't see.  It's probably some basic SQL thing.  Your help 
is appreciated.


create table my_table (
    listid char(36) not null,
    seq smallint not null,
    item varchar(4096),
    primary key (listid, seq)
);

insert into my_table (listid, seq) values ('abc', 1);
insert into my_table (listid, seq) values ('abc', 2);

-- the following works some of the time
update my_table set seq=seq+1;

-- the following doe not work for reasons I do not know
update my_table set seq=seq+1 where listid='abc';

What I get is a duplicate primary key.  I wouldn't think I'd get that 
because I'd think the whole thing is done in a transaction so that 
duplicate checks wouldn't be done till the end (essentially).


Is there a clean way to do this?


A deferrable constraint might solve the problem.

https://www.commandprompt.com/blog/postgres_deferred_primary_keys/


https://www.postgresql.org/docs/9.6/sql-altertable.html

ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE INITIALLY DEFERRED;



--
Angular momentum makes the world go 'round.

Re: Trouble incrementing a column

[Tom Lane]

Blake McBride  writes:
> I am using PostgreSQL 10.10.  I am having trouble incrementing a column for
> reasons I can't see.  It's probably some basic SQL thing.  Your help is
> appreciated.

> create table my_table (
> listid char(36) not null,
> seq smallint not null,
> item varchar(4096),
> primary key (listid, seq)
> );

> insert into my_table (listid, seq) values ('abc', 1);
> insert into my_table (listid, seq) values ('abc', 2);

> -- the following works some of the time
> update my_table set seq=seq+1;

> -- the following doe not work for reasons I do not know
> update my_table set seq=seq+1 where listid='abc';

> What I get is a duplicate primary key.  I wouldn't think I'd get that
> because I'd think the whole thing is done in a transaction so that
> duplicate checks wouldn't be done till the end (essentially).

Postgres only treats primary/unique keys that way if you explicitly
mark the constraint as DEFERRABLE.  Otherwise, the uniqueness check is
made immediately as each row is updated, so it's very order-dependent
as to whether something like the above will work.

Note that you pay a fairly substantial performance penalty for deferring
the check, which is why it isn't the default, even though the SQL spec
says it ought to be.

This is documented in some obscure place [ ... looks around ... ]
ah, see "Non-Deferred Uniqueness Constraints" under Compatibility
in the CREATE TABLE reference page.

regards, tom lane