Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

[Tom Lane]

Alastair McKinley  writes:
> Thank you for having a look at this.  In the interim I discovered that I 
> could trigger the issue by creating a security barrier view, whereas a 
> regular view worked fine, so I think that also points to your conclusion 
> about leakyness?
> I attempted to workaround the issue with a leakproof function, so far with no 
> success.
> ...
> Is this an approach that could fundamentally work?

Forcing the expression to be considered leakproof should work.
I'm not sure that your partial index is OK for the purpose of
collecting stats, though -- does it help if you make a non-partial
index on that function expression?  Otherwise, it's possible that
I guessed wrong about which part of the WHERE clause is problematic.
You could try doing EXPLAINs with different portions of the WHERE
to see how the rowcount estimate changes.

BTW, just marking something "leakproof" when it isn't really so
is possibly a security problem.  You should think twice about
what threat model you're hoping RLS will protect against.

regards, tom lane

Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

[Alastair McKinley]

Hi Tom,

Thank you for having a look at this.  In the interim I discovered that I could 
trigger the issue by creating a security barrier view, whereas a regular view 
worked fine, so I think that also points to your conclusion about leakyness?

I attempted to workaround the issue with a leakproof function, so far with no 
success.

I tried a leakproof function as below:

create or replace function jsonb_select(obj jsonb,keys text[]) returns jsonb as
$$
select jsonb_agg(value order by key) from jsonb_each(obj) where keys @> 
ARRAY[key];
$$ language sql immutable strict leakproof;

And created the expression indexes:

create unique index i_10 on testing ((jsonb_select(data,'{value}'))) where 
type_id = 10 and latest is true;

But my query still produces a bad plan (bitmap heap scan) with rls or a 
security barrier view enabled:

explain (analyze) select * from testing where (jsonb_select(data,'{value}')) = 
to_jsonb(10) and type_id = 10 and latest is true;

Is this an approach that could fundamentally work?

Best regards,

Alastair


From: Tom Lane 
Sent: 04 March 2020 00:04
To: Alastair McKinley 
Cc: pgsql-general@lists.postgresql.org 
Subject: Re: Poor plan choice with partial unique indexes on jsonb column and 
simple RLS policy (with test script)

Alastair McKinley  writes:
> I have recently encountered a strange poor query plan choice after 
> implementing RLS.
> My table has a number of partial indexes on a jsonb column and the query went 
> from low number of milliseconds to several seconds as the planner chose a 
> different index.
> Simply stated, in the jsonb column case, "using ( (select true) )" instead of 
> "using (true)" produces a bad plan, illustrated below:

If the planner isn't sure you have access to all rows in the table,
that disables some of its ability to estimate where-clause selectivity.
In particular it can't run "leaky" where-clauses against all values in
the table's statistics entries to see how many pass, because a nefarious
user could use that to glean info about what's in the table.  Eyeing your
test query, it looks like the issue is that jsonb "->" isn't leakproof,
so that clause falls back to a default selectivity estimate, and you
get a bad plan as a result.

regards, tom lane

Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

[Tom Lane]

Alastair McKinley  writes:
> I have recently encountered a strange poor query plan choice after 
> implementing RLS.
> My table has a number of partial indexes on a jsonb column and the query went 
> from low number of milliseconds to several seconds as the planner chose a 
> different index.
> Simply stated, in the jsonb column case, "using ( (select true) )" instead of 
> "using (true)" produces a bad plan, illustrated below:

If the planner isn't sure you have access to all rows in the table,
that disables some of its ability to estimate where-clause selectivity.
In particular it can't run "leaky" where-clauses against all values in
the table's statistics entries to see how many pass, because a nefarious
user could use that to glean info about what's in the table.  Eyeing your
test query, it looks like the issue is that jsonb "->" isn't leakproof,
so that clause falls back to a default selectivity estimate, and you
get a bad plan as a result.

regards, tom lane

Re: Detecting which columns a query will modify in a function called by a trigger

[David G. Johnston]

On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver 
wrote:

> On 3/3/20 3:06 PM, David G. Johnston wrote:
> > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver  > > wrote:
> >
> > The link was for automatically updateable views. If you want to do
> > something more involved then see:
> >
> >
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
> >
> >
> > CREATE TRIGGER works with views; I usually see recommendations to start
> > there and avoid rules if at all possible.
>
> The above suggests triggers then rules.
>

Yeah, I see the buried in there.  The link itself and the page itself is
something like 95% rules coverage so it still seems worth pointing out even
in hindsight.

Maybe add a link to the CREATE TRIGGER section in there...

David J.

Re: Detecting which columns a query will modify in a function called by a trigger

[Adrian Klaver]

On 3/3/20 3:06 PM, David G. Johnston wrote:
On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver > wrote:


The link was for automatically updateable views. If you want to do
something more involved then see:

https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE


CREATE TRIGGER works with views; I usually see recommendations to start 
there and avoid rules if at all possible.


The above suggests triggers then rules.



David J.



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

Re: Detecting which columns a query will modify in a function called by a trigger

[David G. Johnston]

On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver 
wrote:

> The link was for automatically updateable views. If you want to do
> something more involved then see:
>
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE


CREATE TRIGGER works with views; I usually see recommendations to start
there and avoid rules if at all possible.

David J.

Re: Detecting which columns a query will modify in a function called by a trigger

[Adrian Klaver]

On 3/3/20 1:32 PM, stan wrote:
Please reply to list also.
Ccing list

On Tue, Mar 03, 2020 at 10:48:29AM -0800, Adrian Klaver wrote:

On 3/3/20 9:42 AM, stan wrote:

On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:

On Mon, Mar 2, 2020 at 1:28 PM stan  wrote:


Envision a table with a good many columns. This table represents the "life
history" of a part on a project. Some of the columns need to be
created/modified by the engineer. Some need to be created/modified by the
purchasing agent, some of the columns need to be created by the receiving
department, some of the columns need to be created/modified by the accounts
payable department.

Make sense?



On a theory level this design is insufficiently normalized.  The fact that
you are having issues and challenges working with it suggests you should
seriously consider a different design, one that exhibits better
normalization properties.

Alternatively you might consider just removing direct access to the table
and provide views and/or functions that can use normal permission grants.
Add some check constraints to the table to describe and enforce the
inter-field relationships that are present.



Thanks for the input.

I have, indeed created views that restrict the subset of columns that a
particular job function needs access to to the appropriate ones, but
unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
through a view.

Am I suffering from a lack of knowledge here?


Yes:

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

Updatable Views



OK, looking at that makes me think it is fairly limited. EG an Updatable
view can have on one FROM clause. Thus it by definition cannot use a JOIN.
So if I have a table with may columns, I can create a view that is a subset
of the available columns, which might be a way to address my issue.


The link was for automatically updateable views. If you want to do 
something more involved then see:


https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE



Thanks.




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

Re: Graphical Query Builder

[Adrian Klaver]

On 3/3/20 2:05 PM, Patrick Olson wrote:
Is the Graphical Query Builder supported in pgAdmin 4? I reviewed the 
archives and found a message from 2017 that pgAdmin 4 did not support 
the Graphical Query Builder. Is the Graphical Query Builder still not 
supported by pgAdmin 4? If it isn't supported will it ever be? What are 
the reasons for not supporting the Graphical Query Builder? Why was it 
supported in pgAdmin III and not pgAdmin 4?


You might get an answer sooner on this list:

https://www.postgresql.org/list/pgadmin-support/



Thanks.




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

Re: elog() plperl function

[stan]

On Tue, Mar 03, 2020 at 05:31:32PM -0500, stan wrote:
> I did a Google search for using the RAISE functionality in plperl, and all
> the answers I see point to the elog built in function. Looking at this it
> appears to me, I can do things like RAISE NOTICE. But what if I want to do,
> say, a RAISE EXCEPTION? Or do I need to handle that with the rerun AND an
> elog()?
> 

Never mind, I see in the docs how it works.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

elog() plperl function

[stan]

I did a Google search for using the RAISE functionality in plperl, and all
the answers I see point to the elog built in function. Looking at this it
appears to me, I can do things like RAISE NOTICE. But what if I want to do,
say, a RAISE EXCEPTION? Or do I need to handle that with the rerun AND an
elog()?

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

Graphical Query Builder

[Patrick Olson]

Is the Graphical Query Builder supported in pgAdmin 4? I reviewed the
archives and found a message from 2017 that pgAdmin 4 did not support the
Graphical Query Builder. Is the Graphical Query Builder still not supported
by pgAdmin 4? If it isn't supported will it ever be? What are the reasons
for not supporting the Graphical Query Builder? Why was it supported in
pgAdmin III and not pgAdmin 4?

Thanks.

Re: Examing cotets of NEW & OLD in a function programed in perl

[Christophe Pettus]

> On Mar 3, 2020, at 13:26, stan  wrote:
> So, they should just be visible as OLD, and NEW as hasshes?

They're documented here:

https://www.postgresql.org/docs/current/plperl-triggers.html

A global hash variable $_TD is available in the trigger function with all sorts 
of trigger-related info.

--
-- Christophe Pettus
   x...@thebuild.com

Re: Examing cotets of NEW & OLD in a function programed in perl

[stan]

On Tue, Mar 03, 2020 at 12:59:00PM -0500, Tom Lane wrote:
> stan  writes:
> > I need to write a generic function to process data before allowing the
> > insert or update to continue.
> > To do this, I need to be able to examine the NEW, and OLD structures
> > without prior knowledge of the structure of the table that fired the
> > trigger.
> 
> AFAIR, they're just hashes in Perl, so you should be able to do
> stuff like iterating over the hash keys.
> 
>   regards, tom lane

So, they should just be visible as OLD, and NEW as hasshes?

Sorry for my lack of understnading.

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

Re: Exportacion por lotes

[Alvaro Herrera]

On 2020-Mar-03, Hernan Jesus Gonzalez Carmona wrote:

> Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me
> acabo de inscribir en esta lista de correo y desde ya me disculpo si en
> este mensaje violo alguna normativa de la lista de correo pero necesito
> ayuda que me apura mucho.

I replied in Spanish copying pgsql-es-ayuda.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Exportacion por lotes

[Charles Clavadetscher]

Hola



---
Charles Clavadetscher
Spitzackerstrasse 9
CH - 8057 Zürich

Tel: +41-79-345 18 88
-
> On 03.03.2020, at 20:21, Hernan Jesus Gonzalez Carmona 
>  wrote:
> 
> Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me acabo 
> de inscribir en esta lista de correo y desde ya me disculpo si en este 
> mensaje violo alguna normativa de la lista de correo pero necesito ayuda que 
> me apura mucho.
> 
> Quien me podria ayudar con información respecto de como exportar una consulta 
> en distintos archivos según una condición determinada y que cada archivo 
> tenga el nombre de dicha condición, es decir, si tengo una tabla con 100 
> registros y uno de los campos tiene un dominio de 4 valor distintos, necesito 
> generar 4 archivos cada uno con nombre de cada valor posible de dicho campo, 
> y que la suma de los registros de los 4 archivos sea 100
> 
> ¿me explico?
> 
> Desde ya muy agradecido por vuestro tiempo
> 
> HJGC

Hay una lista de correo en español.
En esta el idioma en uso es el inglés.

About your question.
You can export data to a file using copy or \copy (if you work fro a remote 
client). You can use a select statement that gives you the exact result that 
you need.

For example. Let's assume that you have a table (e.g. t) with a column that you 
want to use for grouping (e.g. group_c). This is basically your use case. Then 
you can do the following in psql:

\copy (select * from t where group_c = 'some value') to filename.csv csv header 
delimiter ';' null ''

The exact syntax is on the postgresql page.

Unfortunately I have no access right now to a PC and can't check for links. I 
will catch that up later if no one else responds.

Regards
Charles

Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

[Alastair McKinley]

Hi all,

I have recently encountered a strange poor query plan choice after implementing 
RLS.

My table has a number of partial indexes on a jsonb column and the query went 
from low number of milliseconds to several seconds as the planner chose a 
different index.

Simply stated, in the jsonb column case, "using ( (select true) )" instead of 
"using (true)" produces a bad plan, illustrated below:

postgres=# create policy testing_s ON testing for select to testing_user using (
postgres(# true
postgres(# );

postgres=# set role testing_user;
SET

postgres=> explain (analyze) select * from testing where data->'value' = 
to_jsonb(10) and type_id = 10 and latest is true;
  QUERY PLAN
---
 Index Scan using i_10 on testing  (cost=0.15..8.17 rows=1 width=49) (actual 
time=0.007..0.008 rows=1 loops=1)
   Index Cond: ((data -> 'value'::text) = to_jsonb(10))
 Planning Time: 0.221 ms
 Execution Time: 0.017 ms
(4 rows)

postgres=# alter policy testing_s ON testing to testing_user using (
postgres(# (select true)
postgres(# );

postgres=> explain (analyze) select * from testing where data->'value' = 
to_jsonb(10) and type_id = 10 and latest is true;
QUERY PLAN
--
 Bitmap Heap Scan on testing  (cost=9.16..17582.89 rows=1 width=49) (actual 
time=0.088..0.877 rows=1 loops=1)
   Recheck Cond: ((type_id = 10) AND (latest IS TRUE))
   Filter: ($0 AND ((data -> 'value'::text) = to_jsonb(10)))
   Rows Removed by Filter: 199
   Heap Blocks: exact=185
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 
rows=1 loops=1)
   ->  Bitmap Index Scan on i_10  (cost=0.00..9.14 rows=7500 width=0) (actual 
time=0.012..0.012 rows=200 loops=1)
 Planning Time: 0.306 ms
 Execution Time: 0.894 ms
(10 rows)

Tested on PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

My two full tests cases are linked below, the first works as expected, the 
second produces a bad plan:

https://gist.github.com/a-mckinley/94a4ada1e40bf79e134a90349cd2a380
https://gist.github.com/a-mckinley/d98fec0fb48a1b8eea3adc526981fb5b

This problem seems to make row level security unusable for me, I am missing 
something in regards to RLS and indexes on jsonb?

Alastair

Re: Detecting which columns a query will modify in a function called by a trigger

[Hellmuth Vargas]

hello

By means of json you can detect what change between NEW and OLD

example:


   select b.*
   from
   (values (now(),123456,'pepito perez',false)) as
 old(dato1,dato2,dato3,dato4),json_each_text(row_to_json(old))  as
b(text1,text2)
   except
   select b.*
   from
   (values (now(),98765,'pepito perez',true)) as
 new(dato1,dato2,dato3,dato4),json_each_text(row_to_json(new))  as
b(text1,text2)



El mar., 3 de mar. de 2020 a la(s) 13:48, Adrian Klaver (
adrian.kla...@aklaver.com) escribió:

> On 3/3/20 9:42 AM, stan wrote:
> > On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
> >> On Mon, Mar 2, 2020 at 1:28 PM stan  wrote:
> >>
> >>> Envision a table with a good many columns. This table represents the
> "life
> >>> history" of a part on a project. Some of the columns need to be
> >>> created/modified by the engineer. Some need to be created/modified by
> the
> >>> purchasing agent, some of the columns need to be created by the
> receiving
> >>> department, some of the columns need to be created/modified by the
> accounts
> >>> payable department.
> >>>
> >>> Make sense?
> >>>
> >>
> >> On a theory level this design is insufficiently normalized.  The fact
> that
> >> you are having issues and challenges working with it suggests you should
> >> seriously consider a different design, one that exhibits better
> >> normalization properties.
> >>
> >> Alternatively you might consider just removing direct access to the
> table
> >> and provide views and/or functions that can use normal permission
> grants.
> >> Add some check constraints to the table to describe and enforce the
> >> inter-field relationships that are present.
> >>
> >
> > Thanks for the input.
> >
> > I have, indeed created views that restrict the subset of columns that a
> > particular job function needs access to to the appropriate ones, but
> > unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
> > through a view.
> >
> > Am I suffering from a lack of knowledge here?
>
> Yes:
>
> https://www.postgresql.org/docs/12/sql-createview.html
>
> Updatable Views
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

-- 
Cordialmente,

Ing. Hellmuth I. Vargas S.

Exportacion por lotes

[Hernan Jesus Gonzalez Carmona]

Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me
acabo de inscribir en esta lista de correo y desde ya me disculpo si en
este mensaje violo alguna normativa de la lista de correo pero necesito
ayuda que me apura mucho.

Quien me podria ayudar con información respecto de como exportar una
consulta en distintos archivos según una condición determinada y que cada
archivo tenga el nombre de dicha condición, es decir, si tengo una tabla
con 100 registros y uno de los campos tiene un dominio de 4 valor
distintos, necesito generar 4 archivos cada uno con nombre de cada valor
posible de dicho campo, y que la suma de los registros de los 4 archivos
sea 100

¿me explico?

Desde ya muy agradecido por vuestro tiempo

HJGC

Re: Detecting which columns a query will modify in a function called by a trigger

[Adrian Klaver]

On 3/3/20 9:42 AM, stan wrote:

On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:

On Mon, Mar 2, 2020 at 1:28 PM stan  wrote:


Envision a table with a good many columns. This table represents the "life
history" of a part on a project. Some of the columns need to be
created/modified by the engineer. Some need to be created/modified by the
purchasing agent, some of the columns need to be created by the receiving
department, some of the columns need to be created/modified by the accounts
payable department.

Make sense?



On a theory level this design is insufficiently normalized.  The fact that
you are having issues and challenges working with it suggests you should
seriously consider a different design, one that exhibits better
normalization properties.

Alternatively you might consider just removing direct access to the table
and provide views and/or functions that can use normal permission grants.
Add some check constraints to the table to describe and enforce the
inter-field relationships that are present.



Thanks for the input.

I have, indeed created views that restrict the subset of columns that a
particular job function needs access to to the appropriate ones, but
unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
through a view.

Am I suffering from a lack of knowledge here?


Yes:

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

Updatable Views



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

Re: Examing cotets of NEW & OLD in a function programed in perl

[Tom Lane]

stan  writes:
> I need to write a generic function to process data before allowing the
> insert or update to continue.
> To do this, I need to be able to examine the NEW, and OLD structures
> without prior knowledge of the structure of the table that fired the
> trigger.

AFAIR, they're just hashes in Perl, so you should be able to do
stuff like iterating over the hash keys.

regards, tom lane

Re: Detecting which columns a query will modify in a function called by a trigger

[stan]

On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
> On Mon, Mar 2, 2020 at 1:28 PM stan  wrote:
> 
> > Envision a table with a good many columns. This table represents the "life
> > history" of a part on a project. Some of the columns need to be
> > created/modified by the engineer. Some need to be created/modified by the
> > purchasing agent, some of the columns need to be created by the receiving
> > department, some of the columns need to be created/modified by the accounts
> > payable department.
> >
> > Make sense?
> >
> 
> On a theory level this design is insufficiently normalized.  The fact that
> you are having issues and challenges working with it suggests you should
> seriously consider a different design, one that exhibits better
> normalization properties.
> 
> Alternatively you might consider just removing direct access to the table
> and provide views and/or functions that can use normal permission grants.
> Add some check constraints to the table to describe and enforce the
> inter-field relationships that are present.
> 

Thanks for the input.

I have, indeed created views that restrict the subset of columns that a
particular job function needs access to to the appropriate ones, but
unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
through a view.

Am I suffering from a lack of knowledge here?

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

Examing cotets of NEW & OLD in a function programed in perl

[stan]

I need to write a generic function to process data before allowing the
insert or update to continue.

To do this, I need to be able to examine the NEW, and OLD structures
without prior knowledge of the structure of the table that fired the
trigger.

Can someone show me an example of how to get these structures into
something I can work with in Perl?


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

Re: Perl::DBI and TYPE of column

[Christoph Moench-Tegeder]

## Matthias Apitz (g...@unixarea.de):

> My question here is: How I could get a copy of the document 
> ftp://sqlstandards.org/SC32/SQL_Registry/

Methinks that the most interesting constants of that are already in
DBI (export tag sql_types) - man DBI, /sql_types. Is that the data
you're looking for? Also look at DBD::Pg, pg_types.

Regards,
Christoph

-- 
Spare Space

Re: Perl::DBI and TYPE of column

[Adrian Klaver]

On 3/3/20 6:02 AM, Matthias Apitz wrote:

Hello,

We unload Sybase and Oracle data to migrate the database to PostgreSQL.
The loading is done very fast with PostgreSQL's COPY command.

During unload trailing blanks in all columns are discarded, because they
would cause problems during loading for INT and DATE columns. The
discarding is done like this after fetching the row into the array
@row_ary:

 ...
 # SRP-25024: support for PostgreSQL: we remove on export trailing blanks
 foreach my $i (0..$#row_ary) {
 $row_ary[$i] =~ s/\s+$//;
 # but for CHAR columns we keep one
 # print $dba->{'sth'}->{NAME}->[$i] . " " . $dba->{'sth'}->{TYPE}->[$i] . 
"\n";
 # it seems that VARCHAR in Sybase is TYPE=1 and in Oracle TYPE=12
 # see also 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/CIHHGDBC.htm
 # and ftp://sqlstandards.org/SC32/SQL_Registry/
 #
 if ($dba->{'sth'}->{TYPE}->[$i] == 1 || $dba->{'sth'}->{TYPE}->[$i] == 
12)  {
 $row_ary[$i] =~ s/^$/ /;
 }
 }

My question here is: How I could get a copy of the document
ftp://sqlstandards.org/SC32/SQL_Registry/

Any copy available here in this list? Thanks


All I could find:

https://grokbase.com/t/perl/dbi-users/074q99ddsn/registry-of-values-for-ansi-x3-135-and-iso-iec-9075-sql-standards




matthias




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

Re: Perl::DBI and TYPE of column

[Matthias Apitz]

El día Dienstag, März 03, 2020 a las 09:36:32 -0500, Tom Lane escribió:

> Matthias Apitz  writes:
> > During unload trailing blanks in all columns are discarded, because they
> > would cause problems during loading for INT and DATE columns.
> 
> Really?
> 
> regression=# select '123  '::int;
>  int4 
> --
>   123
> (1 row)
> 
> regression=# select '12-02-2019  '::date;
> date
> 
>  2019-12-02
> (1 row)

The problem occurs when loading CSV data like ...||... into an INT
column with COPY. I could make you an exact example.

But this wasn't my question, my question is where the document is.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Perl::DBI and TYPE of column

[Tom Lane]

Matthias Apitz  writes:
> During unload trailing blanks in all columns are discarded, because they
> would cause problems during loading for INT and DATE columns.

Really?

regression=# select '123  '::int;
 int4 
--
  123
(1 row)

regression=# select '12-02-2019  '::date;
date

 2019-12-02
(1 row)

regards, tom lane

Perl::DBI and TYPE of column

[Matthias Apitz]

Hello,

We unload Sybase and Oracle data to migrate the database to PostgreSQL.
The loading is done very fast with PostgreSQL's COPY command.

During unload trailing blanks in all columns are discarded, because they
would cause problems during loading for INT and DATE columns. The
discarding is done like this after fetching the row into the array
@row_ary:

...
# SRP-25024: support for PostgreSQL: we remove on export trailing blanks
foreach my $i (0..$#row_ary) {
$row_ary[$i] =~ s/\s+$//;
# but for CHAR columns we keep one
# print $dba->{'sth'}->{NAME}->[$i] . " " . $dba->{'sth'}->{TYPE}->[$i] 
. "\n"; 
# it seems that VARCHAR in Sybase is TYPE=1 and in Oracle TYPE=12
# see also 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/CIHHGDBC.htm
# and ftp://sqlstandards.org/SC32/SQL_Registry/
#
if ($dba->{'sth'}->{TYPE}->[$i] == 1 || $dba->{'sth'}->{TYPE}->[$i] == 
12)  {
$row_ary[$i] =~ s/^$/ /;
}
}

My question here is: How I could get a copy of the document 
ftp://sqlstandards.org/SC32/SQL_Registry/

Any copy available here in this list? Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Postgres on macOS 10

[Nick Renders]

Hi,

We just noticed something strange with our Postgres server.

We have Postgres 11 and 12 running on macOS 10.14 (Mojave), installed 
with the EDB installer. Whenever the machine is restarted, the Postgres 
service cannot be launched until a macOS user logs in. We have 
"automatic login" turned off in the system preferences, meaning that 
when the machine reboots, you get the macOS login screen.


In the system.log we see the following 2 lines over and over again, 
until a user has logged in:


	Mar  3 09:37:19 postgrestest com.apple.xpc.launchd[1] 
(com.edb.launchd.postgresql-12[319]): Service exited with abnormal code: 
2
	Mar  3 09:37:19 postgrestest com.apple.xpc.launchd[1] 
(com.edb.launchd.postgresql-12): Service only ran for 0 seconds. Pushing 
respawn out by 10 seconds.



It doesn't matter which macOS user logs in, doesn't have to be an 
Administrator. But once a user has logged in, the Postgres service is 
finally launched properly. Afterwards, the macOS user can log out again, 
and the service will continue running.


It doesn't seem to be limited to Mojave, either. I did a quick test on 
our older, decommissioned Postgres server (Postgres 9 on macOS 10.12) 
and the same issue seems to occur there.


Has anyone noticed something similar with macOS? Or is it just our 
setup?


Best regards,

Nick Renders