Re: Splitting libpq build

[Igor Korot]

Hi, Tom,

On Sun, Oct 3, 2021 at 5:27 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > Imagine you are a developer/maintainer of sme software. This software needs 
> > to
> > connect to different DBMSes perform soe operations and then disconnect.
> > It is very easy to say that I have SQLite as a submodule in my Git tree,
> > I have mysql-connector as a submodule, but I can't have libpq this way
> > because it is hidden deep down inside PostgreSQL sources.
>
> To be blunt, that is an absolutely horrid way to distribute software.
> You should not, not, not be redistributing other people's code,
> because that puts you on the hook to keep up with their bug fixes ---
> particularly security-critical fixes, which do happen from time to time.
> That's a loop you don't want to be in.

I am not.
I don't know if you are familiar, but Git has a notion of using submodules
Which means all I as a developer can do is to say "My program is using moduleA,
moduleB and moduleC". And if the user configure my program not to use
system libraries
but use mine instead (maybe because mine is newer?), I'll let them.

>
> In this case I don't even see what's the point.  If somebody wants to
> use your code with Postgres, it's just about certain that they already
> have an installed copy of libpq, from wherever they get Postgres from.
> The same argument applies to mysql or SQLite.  So you should be
> expecting to link against already-present libraries, not bring your
> own.  Do you bring your own libc, or openssl, or C compiler?

Imagine a situation where someone is using Ubuntu 16 and libpq 5.
I can provide a newer version of libpq because it has security fixes
and sme additional features.
Remember - release life cycle for different distros are different.
And what about Windows?
Usually when you work with Windows you lin statically.
Therefore it doesn't matter much - you provide the code that the user builds
and that's it.

>
> I can tell you for certain that if your package were to be picked up
> and redistributed by Red Hat, or Debian, or any other open-source
> vendor, the very first thing they'd do is strip it of any such copied
> code.  They don't want the management hassle of having to update
> multiple copies of libpq or mysql-connector or other stuff.

Agreed.
But my repository will not contain a copy of libpq - it will merely be
pointing to
the original source code of the library.
See the difference?
And if the user wants to clone my repository - great.
He will just grab the cde from the PostgreSQL repo and build it as
part of the build process.

Thank you.

>
> regards, tom lane

Re: Splitting libpq build

[Tom Lane]

Igor Korot  writes:
> Imagine you are a developer/maintainer of sme software. This software needs to
> connect to different DBMSes perform soe operations and then disconnect.
> It is very easy to say that I have SQLite as a submodule in my Git tree,
> I have mysql-connector as a submodule, but I can't have libpq this way
> because it is hidden deep down inside PostgreSQL sources.

To be blunt, that is an absolutely horrid way to distribute software.
You should not, not, not be redistributing other people's code,
because that puts you on the hook to keep up with their bug fixes ---
particularly security-critical fixes, which do happen from time to time.
That's a loop you don't want to be in.

In this case I don't even see what's the point.  If somebody wants to
use your code with Postgres, it's just about certain that they already
have an installed copy of libpq, from wherever they get Postgres from.
The same argument applies to mysql or SQLite.  So you should be
expecting to link against already-present libraries, not bring your
own.  Do you bring your own libc, or openssl, or C compiler?

I can tell you for certain that if your package were to be picked up
and redistributed by Red Hat, or Debian, or any other open-source
vendor, the very first thing they'd do is strip it of any such copied
code.  They don't want the management hassle of having to update
multiple copies of libpq or mysql-connector or other stuff.

regards, tom lane

Re: Check constraint failure messages

[David G. Johnston]

On Sun, Oct 3, 2021, 14:39 zim  wrote:

> An error message that includes the table name and column name would also
> be helpful for non-bulk inserts:
>

This is not limited to domain types and comes up fairly often.  In general
there is agreement that the current behavior is not great but implementing
something better seems to be more challenging than the perceived benefit
warrants.

David J.

>

Re: Check constraint failure messages

[zim]

An error message that includes the table name and column name would also 
be helpful for non-bulk inserts: a single insert where multiple columns 
of a table have the same domain type. The problem gets worse when there 
are inserts into multiple tables that have the same domain types 
(multiple sql inserts in a statement).


Am 06.04.21 um 22:58 schrieb Ron:

On 4/6/21 3:50 PM, Miles Elam wrote:

On Tue, Apr 6, 2021 at 1:03 PM Ron  wrote:

On 4/6/21 2:40 PM, Miles Elam wrote:

I've got a domain that validates email addresses. When inserting
a bunch of entries I simply get the error message

ERROR: value for domain po.email violates check constraint
"email_check" SQL state: 23514


When inserting 1000+ entries in a batch, finding the exact entry
with the problem is noticeably harder than with other error
types. For example when a column should be a uuid but you pass
in 'Mary had a little lamb', the error message tells you what
the invalid value is as well as the column name you're trying to
put it into.

Are there any quick hacks floating around out there to solve or
at least mitigate this?


Is it a deferred constraint?

Plain ole domain CHECK constraint.

CREATE DOMAIN po.email AS varchar
  CHECK (VALUE IS NULL OR (po.length_in(VALUE, 1, 254) AND NOT
po.email_expanded(VALUE) IS NULL));


where "po" is another schema, po.length_in(...) is an IMMUTABLE range 
check, and po.email_expanded(...) is a function returning a record. 
Same behavior happens if I remove the functions and define the check 
constraint in place. The only info returned in a bulk insert is the 
name of the violated check constraint, aka email_check.


The blunt force answer is to not use bulk inserts.  Try COPY; it's 
good at saying which record throws an error.


--
Angular momentum makes the world go 'round.

Re: PostgreSQL - Ordering Table based of Foreign Key

[David G. Johnston]

On Sun, Oct 3, 2021, 00:48 FOUTE K. Jaurès  wrote:

> Hello,
>
> I want to order tables based on the foreign key so that I can delete
> tables one by one without facing "ERROR: update or delete on table
> "table" violates foreign key constraint. DETAIL: Key is still referenced
> from table"
>

As others have said this is why on delete cascade exists.  Unfortunately
this does require some advanced planning as changing it on the fly doesn't
really work.

I do not believe there is a built-in way to return an ordered listing of
dependent relations given a base relation as an input.  But the catalog
entries do exist should you wish to build such yourself.

That said maybe deferred constraint evaluation will work so that at least
the order doesn't matter.  But you still.would.need to know which tables to
write delete commands for.

There is some recent discussion on making this work in a more user-friendly
away but that would be only available in v15 at best.

David J.


>

Re: Splitting libpq build

[Igor Korot]

Hi, Adrian,

On Sun, Oct 3, 2021 at 3:28 PM Adrian Klaver  wrote:
>
> On 10/3/21 1:20 PM, Igor Korot wrote:
> > Hi Tom,
> >
> > On Sun, Oct 3, 2021 at 3:11 PM Tom Lane  wrote:
> >>
> >> Igor Korot  writes:
> >>> I wonder who ade the decision not to provide an independent build
> >>> of the client library of PostgreSQL? And what was the reason behind it?
>
> > But building the whole tree is just that - useless.
> > All I need is the libpq to connect to the remote server.
> >
> > So I don't need the server to be built - I need a client.
>
> That is available from many of the packaging systems.
>
> >
> >>
> >> Having said that, it does work on non-Windows machines to do
> >>  ./configure ...
> >>  cd src/interfaces/libpq
> >>  make -s install
> >
> > Well, asking people to download the whole source tree just to build
> > client library 1000 times and then 1000 times delete the source code...
>
> If you are deploying to the same client you only need to build once.
>
> If not then you need to be more specific about what you are doing?

Imagine you are a developer/maintainer of sme software. This software needs to
connect to different DBMSes perform soe operations and then disconnect.

It is very easy to say that I have SQLite as a submodule in my Git tree,
I have mysql-connector as a submodule, but I can't have libpq this way
because it is hidden deep down inside PostgreSQL sources.

So in order too package the software and let the users build it on
their machine,
I need to explicitly document that the user need to get the PostgreSQL sources,
build it, copy the headers and libraries as appropriate and then build
the software
I provide.

Do you know of a better way of handling this?

O, and of course I need this on all 3 major platforms - Windows, nix and OSX.

Thank you.

>
> >
> > I don't know - I'd rather have to install just the library.
> >
>
> > So instead of simplifying the life of maintainers in the different distros
> > you made the life easier for yourself. ;;-)
>
> Not following:
>
> apt-cache show libpq5
> Package: libpq5
> Source: postgresql-14
> Version: 14.0-1.pgdg20.04+1
> Architecture: amd64
> Maintainer: Debian PostgreSQL Maintainers
> 
> Installed-Size: 748
> Depends: libc6 (>= 2.25), libgssapi-krb5-2 (>= 1.17), libldap-2.4-2 (>=
> 2.4.7), libssl1.1 (>= 1.1.0)
> Multi-Arch: same
> Homepage: http://www.postgresql.org/
> Priority: optional
> Section: libs
> Filename: pool/main/p/postgresql-14/libpq5_14.0-1.pgdg20.04+1_amd64.deb
> Size: 169756
> SHA256: 59be24749b3ee996af762dfad6ac8e915498f0bdb431612308548bac6a5324e0
> SHA1: d002223d362a45959a7832bdddbd2ec04c4611ba
> MD5sum: 2834edac0af08b7ffb4cc5093bb236b6
> Description-en: PostgreSQL C client library
>   libpq is a C library that enables user programs to communicate with
>   the PostgreSQL database server.  The server can be on another machine
>   and accessed through TCP/IP.  This version of libpq is compatible
>   with servers from PostgreSQL 8.2 or later.
>   .
>   This package contains the run-time library, needed by packages using
>   libpq.
>   .
>   PostgreSQL is an object-relational SQL database management system.
> Description-md5: 4109d08c168c8058d09c0cc8bf8b18b1
>
> >
> > Thank you.
> >
> >>
> >>  regards, tom lane
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: Splitting libpq build

[Tom Lane]

Igor Korot  writes:
> On Sun, Oct 3, 2021 at 3:11 PM Tom Lane  wrote:
>> Having said that, it does work on non-Windows machines to do
>> ./configure ...
>> cd src/interfaces/libpq
>> make -s install

> Well, asking people to download the whole source tree just to build
> client library 1000 times and then 1000 times delete the source code...

Uh ... why are you installing from source at all, if that's your
use-case?  Most major distributions provide packages that subdivide
Postgres into client and server packages, and usually there's even
a package with just the library(s).

> So instead of simplifying the life of maintainers in the different distros
> you made the life easier for yourself. ;;-)

Well, yeah.  Packaging is a different business.  And, having *been*
a packager for Red Hat for a decade, it's not like I've never heard
of it.  But I don't see anything we need to do differently there.

regards, tom lane

Re: Splitting libpq build

[Adrian Klaver]

On 10/3/21 1:20 PM, Igor Korot wrote:

Hi Tom,

On Sun, Oct 3, 2021 at 3:11 PM Tom Lane  wrote:


Igor Korot  writes:

I wonder who ade the decision not to provide an independent build
of the client library of PostgreSQL? And what was the reason behind it?



But building the whole tree is just that - useless.
All I need is the libpq to connect to the remote server.

So I don't need the server to be built - I need a client.


That is available from many of the packaging systems.





Having said that, it does work on non-Windows machines to do
 ./configure ...
 cd src/interfaces/libpq
 make -s install


Well, asking people to download the whole source tree just to build
client library 1000 times and then 1000 times delete the source code...


If you are deploying to the same client you only need to build once.

If not then you need to be more specific about what you are doing?



I don't know - I'd rather have to install just the library.




So instead of simplifying the life of maintainers in the different distros
you made the life easier for yourself. ;;-)


Not following:

apt-cache show libpq5
Package: libpq5
Source: postgresql-14
Version: 14.0-1.pgdg20.04+1
Architecture: amd64
Maintainer: Debian PostgreSQL Maintainers 


Installed-Size: 748
Depends: libc6 (>= 2.25), libgssapi-krb5-2 (>= 1.17), libldap-2.4-2 (>= 
2.4.7), libssl1.1 (>= 1.1.0)

Multi-Arch: same
Homepage: http://www.postgresql.org/
Priority: optional
Section: libs
Filename: pool/main/p/postgresql-14/libpq5_14.0-1.pgdg20.04+1_amd64.deb
Size: 169756
SHA256: 59be24749b3ee996af762dfad6ac8e915498f0bdb431612308548bac6a5324e0
SHA1: d002223d362a45959a7832bdddbd2ec04c4611ba
MD5sum: 2834edac0af08b7ffb4cc5093bb236b6
Description-en: PostgreSQL C client library
 libpq is a C library that enables user programs to communicate with
 the PostgreSQL database server.  The server can be on another machine
 and accessed through TCP/IP.  This version of libpq is compatible
 with servers from PostgreSQL 8.2 or later.
 .
 This package contains the run-time library, needed by packages using
 libpq.
 .
 PostgreSQL is an object-relational SQL database management system.
Description-md5: 4109d08c168c8058d09c0cc8bf8b18b1



Thank you.



 regards, tom lane






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

Re: Splitting libpq build

[Igor Korot]

Hi Tom,

On Sun, Oct 3, 2021 at 3:11 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > I wonder who ade the decision not to provide an independent build
> > of the client library of PostgreSQL? And what was the reason behind it?
>
> It wasn't worth the maintenance trouble.  It still isn't.  On any
> remotely-modern machine, a build of the whole tree is only going
> to take a minute or two.  And libpq doesn't change so fast that
> you'd need to do this often.

But building the whole tree is just that - useless.
All I need is the libpq to connect to the remote server.

So I don't need the server to be built - I need a client.

>
> Having said that, it does work on non-Windows machines to do
> ./configure ...
> cd src/interfaces/libpq
> make -s install

Well, asking people to download the whole source tree just to build
client library 1000 times and then 1000 times delete the source code...

I don't know - I'd rather have to install just the library.

>
> The Windows case is exactly what we killed as not being worth
> the maintenance effort, so I doubt you'll find much interest
> in resurrecting that one.

So instead of simplifying the life of maintainers in the different distros
you made the life easier for yourself. ;;-)

Thank you.

>
> regards, tom lane

Re: Splitting libpq build

[Tom Lane]

Igor Korot  writes:
> I wonder who ade the decision not to provide an independent build
> of the client library of PostgreSQL? And what was the reason behind it?

It wasn't worth the maintenance trouble.  It still isn't.  On any
remotely-modern machine, a build of the whole tree is only going
to take a minute or two.  And libpq doesn't change so fast that
you'd need to do this often.

Having said that, it does work on non-Windows machines to do
./configure ...
cd src/interfaces/libpq
make -s install

The Windows case is exactly what we killed as not being worth
the maintenance effort, so I doubt you'll find much interest
in resurrecting that one.

regards, tom lane

Splitting libpq build

[Igor Korot]

Hi, ALL,
I wonder who ade the decision not to provide an independent build
of the client library of PostgreSQL? And what was the reason behind it?

Its very annoying to build the whole server, just to create a client library
to connect to a remote DB. And then kill the build after the install as
useless.

Could someone please recommend a best way to have it build?

Thank you.

Re: PostgreSQL CHECK Constraint

[Karsten Hilbert]

GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
--
Karsten

Sure, they are just a search engine's use away.

> Are there any examples on the web these days?
> That sounds brilliant.

:Am Sun, Oct 03, 2021 at 07:44:41PM +0100 schrieb Shaozhong SHI

Re: PostgreSQL CHECK Constraint

[Shaozhong SHI]

Hi, Christian,
Brilliant!
Some years ago, I did something similar and saved problematic ones for data
collector.
Now, I am reviewing to see whether there be any elegant way to automate
reporting and giving feedback.
Regards,
David

On Sunday, 3 October 2021, Christian Ramseyer  wrote:

> > The reported error looks like this:
> >
> > postgres@dellstore ERROR:  new row for relation "test_customers"
> > violates check constraint "check_age"
> ..
> >
> > This errors appears in the serverlog which has many format and
> > forwarding options, you can read about them here:
>
> On 03.10.21 20:16, Shaozhong SHI wrote:
> > Hi， Christian,
> > That is interesting.  Can errors be captured and saved as data with
> > scripting?
>
> Yes that works quite the same, e.g. in Python you can do
>
> try:
> cur.execute("insert into test_customers (firstname, lastname, age)
> values ( %s, %s, %s)", ("Bobby", "Tables", 10))
> except psycopg2.errors.CheckViolation as e:
> print(f"That didn't work: {e.cursor.query} failed")
> print(f"{e.pgerror}")
>
>
> HTH
>

Re: PostgreSQL CHECK Constraint

[Christian Ramseyer]

> The reported error looks like this:
>
> postgres@dellstore ERROR:  new row for relation "test_customers"
> violates check constraint "check_age"
..
>
> This errors appears in the serverlog which has many format and
> forwarding options, you can read about them here:

On 03.10.21 20:16, Shaozhong SHI wrote:
> Hi， Christian,
> That is interesting.  Can errors be captured and saved as data with
> scripting?

Yes that works quite the same, e.g. in Python you can do

try:
cur.execute("insert into test_customers (firstname, lastname, age)
values ( %s, %s, %s)", ("Bobby", "Tables", 10))
except psycopg2.errors.CheckViolation as e:
print(f"That didn't work: {e.cursor.query} failed")
print(f"{e.pgerror}")


HTH

Re: PostgreSQL CHECK Constraint

[Shaozhong SHI]

Hi, Kirsten,
That sounds brilliant.
Are there any examples on the web these days?
Regards,
David

On Sunday, 3 October 2021, Karsten Hilbert  wrote:

> Am Sun, Oct 03, 2021 at 07:16:32PM +0100 schrieb Shaozhong SHI:
>
> > That is interesting.  Can errors be captured and saved as data with
> > scripting?
>
> Depends on what the script does.
>
> If the script runs (or is written in) Python the canonical PG
> driver (psycopg2/3) will give you such data.
>
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>

Re: PostgreSQL CHECK Constraint

[Karsten Hilbert]

Am Sun, Oct 03, 2021 at 07:16:32PM +0100 schrieb Shaozhong SHI:

> That is interesting.  Can errors be captured and saved as data with
> scripting?

Depends on what the script does.

If the script runs (or is written in) Python the canonical PG
driver (psycopg2/3) will give you such data.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: PostgreSQL CHECK Constraint

[Shaozhong SHI]

Hi， Christian,
That is interesting.  Can errors be captured and saved as data with
scripting?
Regards,
David

On Sunday, 3 October 2021, Christian Ramseyer  wrote:

>
>
> On 03.10.21 09:31, Shaozhong SHI wrote:
> >
> > Has anyone got experience with data quality checking, validation and
> > reporting within PostgreSQL?
> >
> > How best to use PostgreSQL CHECK Constraint for data quality checking,
> > validation and reporting?
> >
> > Can we report on errors in a detailed and specific way?  For instance,
> > can we produce report on specific issues of erroneous cells in which row
> > and etc.?
> >
>
> Yes that's all possible. Given a constraint like
>
> alter table test_customers
>add constraint check_age check (age >= 18);
>
>
> The reported error looks like this:
>
> postgres@dellstore ERROR:  new row for relation "test_customers"
> violates check constraint "check_age"
>
> postgres@dellstore DETAIL:  Failing row contains (1, Jimmy, Schmoe, 15).
>
> postgres@dellstore STATEMENT:  insert into test_customers (firstname,
> lastname, age) values ( 'Jimmy', 'Schmoe', 15);
>
> This errors appears in the serverlog which has many format and
> forwarding options, you can read about them here:
>
> https://www.postgresql.org/docs/current/runtime-config-logging.html
>
> Cheers
> Christian
>
> --
> Christian Ramseyer, netnea ag
> Network Management. Security. OpenSource.
> https://www.netnea.com
>
>

Re: pg_dump save command in output

[Adrian Klaver]

On 10/3/21 10:15 AM, Joao Miguel Ferreira wrote:




I just wanted to save the command inside the SQL file to be able to 
"remember" it later. ideally I would open the file on some text editor 
and see the original pg_dump command on the few first lines, as a comment


-- original command was: psql -h some_host -p .


Are you dumping in plain or custom format?


plain (SQL)


A quick test here found that manually adding:

-- original command was: psql -h some_host -p .

as:

-- Dumped by pg_dump version XX.x
-- original command was: psql -h some_host -p .

at head of file did not infer with the restore.

No guarantees though.





 >
 > thank you
 > Joao
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: pg_dump save command in output

[Karsten Hilbert]

Am Sun, Oct 03, 2021 at 06:15:54PM +0100 schrieb Joao Miguel Ferreira:

> I just wanted to save the command inside the SQL file to be able to
> "remember" it later. ideally I would open the file on some text editor and
> see the original pg_dump command on the few first lines, as a comment
>
> -- original command was: psql -h some_host -p .
>
> >
> > Are you dumping in plain or custom format?
> >
>
> plain (SQL)

Run the dump from a bash script which pre-catenates (Is that
even a word ? it is now :) the command into the plain (SQL)
output after the fact.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: pg_dump save command in output

[Joao Miguel Ferreira]

On Sun, Oct 3, 2021 at 6:03 PM Adrian Klaver 
wrote:

> On 10/3/21 9:56 AM, Joao Miguel Ferreira wrote:
> > Hello all,
> >
> > is there a way to ask pg_dump to save a comment in the output file with
> > the full command it is executing ?
>
> No.
>
> What information is the comment trying to convey?
>

I just wanted to save the command inside the SQL file to be able to
"remember" it later. ideally I would open the file on some text editor and
see the original pg_dump command on the few first lines, as a comment

-- original command was: psql -h some_host -p .


>
> Are you dumping in plain or custom format?
>

plain (SQL)


>
> >
> > thank you
> > Joao
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: pg_dump save command in output

[Adrian Klaver]

On 10/3/21 9:56 AM, Joao Miguel Ferreira wrote:

Hello all,

is there a way to ask pg_dump to save a comment in the output file with 
the full command it is executing ?


No.

What information is the comment trying to convey?

Are you dumping in plain or custom format?



thank you
Joao




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

Re: PostgreSQL - Ordering Table based of Foreign Key

[Adrian Klaver]

On 10/3/21 9:24 AM, FOUTE K. Jaurès wrote:

Please don't top post, inline/bottom posting is the preferred style on 
this list.


I need to delete data from about 100 table in the production system for 
a specific client without stopping application.


Honestly that is not that much more informative.

So:

1) Do the FK's have "ON DELETE CASCADE" already?

2) Does a single FK cascade through all 100 tables or are there groups 
of parent/child tables and associated FK's


3) Show an example DELETE query and tables affected.



On Sun, 3 Oct 2021, 17:19 Adrian Klaver, > wrote:


On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
 > Thanks for your input but I want to make a delete with where clause

Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the
FK would seem to be what you need. If not then you will need to provide
more information about what you are trying to achieve?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

pg_dump save command in output

[Joao Miguel Ferreira]

Hello all,

is there a way to ask pg_dump to save a comment in the output file with the
full command it is executing ?

thank you
Joao

Re: PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

I need to delete data from about 100 table in the production system for a
specific client without stopping application.

On Sun, 3 Oct 2021, 17:19 Adrian Klaver,  wrote:

> On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
> > Thanks for your input but I want to make a delete with where clause
>
> Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the
> FK would seem to be what you need. If not then you will need to provide
> more information about what you are trying to achieve?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PostgreSQL - Ordering Table based of Foreign Key

[Adrian Klaver]

On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:

Thanks for your input but I want to make a delete with where clause


Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the 
FK would seem to be what you need. If not then you will need to provide 
more information about what you are trying to achieve?



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

Re: PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

Thanks for your input but I want to make a delete with where clause

On Sun, 3 Oct 2021, 10:26 Andreas Joseph Krogh,  wrote:

> På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer <
> sham...@gmx.net>:
>
> FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
> > I want to order tables based on the foreign key so that I can delete
> > tables one by one without facing "ERROR: update or delete on table
> > "table" violates foreign key constraint. DETAIL: Key is still
> > referenced from table"
>
> You can create the foreign key constraints with the "ON DELETE CASCADE"
> option.
> Then Postgres will handle dependencies automatically for you.
>
> But that means that *all* DELETEs will be cascaded.
>
> Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)
>
>
> There is TRUNCATE ... CASCADE
> https://www.postgresql.org/docs/14/sql-truncate.html
>
> --
> Andreas Joseph Krogh
>

Re: PostgreSQL CHECK Constraint

[Christian Ramseyer]

On 03.10.21 09:31, Shaozhong SHI wrote:
> 
> Has anyone got experience with data quality checking, validation and
> reporting within PostgreSQL?
> 
> How best to use PostgreSQL CHECK Constraint for data quality checking,
> validation and reporting?  
> 
> Can we report on errors in a detailed and specific way?  For instance,
> can we produce report on specific issues of erroneous cells in which row
> and etc.?
> 

Yes that's all possible. Given a constraint like

alter table test_customers
   add constraint check_age check (age >= 18);


The reported error looks like this:

postgres@dellstore ERROR:  new row for relation "test_customers"
violates check constraint "check_age"

postgres@dellstore DETAIL:  Failing row contains (1, Jimmy, Schmoe, 15).

postgres@dellstore STATEMENT:  insert into test_customers (firstname,
lastname, age) values ( 'Jimmy', 'Schmoe', 15);

This errors appears in the serverlog which has many format and
forwarding options, you can read about them here:

https://www.postgresql.org/docs/current/runtime-config-logging.html

Cheers
Christian

-- 
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com

Re: PostgreSQL - Ordering Table based of Foreign Key

[Andreas Joseph Krogh]

På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer mailto:sham...@gmx.net>>: 
FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
 > I want to order tables based on the foreign key so that I can delete
 > tables one by one without facing "ERROR: update or delete on table
 > "table" violates foreign key constraint. DETAIL: Key is still
 > referenced from table"

 You can create the foreign key constraints with the "ON DELETE CASCADE" 
option.
 Then Postgres will handle dependencies automatically for you.

 But that means that *all* DELETEs will be cascaded.

 Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP) 

There is TRUNCATE ... CASCADE 
https://www.postgresql.org/docs/14/sql-truncate.html 



-- 
Andreas Joseph Krogh 

Re: PostgreSQL - Ordering Table based of Foreign Key

[Thomas Kellerer]

FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:

I want to order tables based on the foreign key so that I can delete
tables one by one without facing "ERROR: update or delete on table
"table" violates foreign key constraint. DETAIL: Key is still
referenced from table"


You can create the foreign key constraints with the "ON DELETE CASCADE" option.
Then Postgres will handle dependencies automatically for you.

But that means that *all* DELETEs will be cascaded.

Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)

Thomas

Re: PostgreSQL - Ordering Table based of Foreign Key

[Karsten Hilbert]

Am Sun, Oct 03, 2021 at 08:48:13AM +0100 schrieb FOUTE K. Jaurès:

> I want to order tables based on the foreign key so that I can delete tables
> one by one without facing "ERROR: update or delete on table "table"
> violates foreign key constraint. DETAIL: Key is still referenced from table"

drop table if exists ... cascade;

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

Hello,

I want to order tables based on the foreign key so that I can delete tables
one by one without facing "ERROR: update or delete on table "table"
violates foreign key constraint. DETAIL: Key is still referenced from table"

Any help is appreciated.
Thank you in advance.

Best regards
Jaurès FOUTE

PostgreSQL CHECK Constraint

[Shaozhong SHI]

Has anyone got experience with data quality checking, validation and
reporting within PostgreSQL?

How best to use PostgreSQL CHECK Constraint for data quality checking,
validation and reporting?

Can we report on errors in a detailed and specific way?  For instance, can
we produce report on specific issues of erroneous cells in which row and
etc.?

Regards,

David