date:20180730


On 07/30/2018 05:57 AM, Achilleas Mantzios wrote:

This is with PostgreSQL 10.4.
How to reproduce :
postgres@smadev:~% psql
psql (10.4)
Type "help" for help.

Alter the role for search path :
dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
ALTER ROLE
dynacom=#

Verify :
postgres@smadev:~% psql -U amura3
Password for user amura3:
psql (10.4)
Type "help" for help.

dynacom=> show search_path ;
     search_path
---
  $user, amuragents, public
(1 row)

dynacom=>

pg_dumpall's output :
ALTER ROLE amura3 SET search_path TO $user, amuragents, public;

psql -f pg_dumpall_out.sql :
dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
ERROR:  syntax error at or near "$"


Well the above does not have $user double quoted. Was that hand entered 
or was that like that in the pg_dumpall_out.sql file?




Is this a bug or am I missing something ?


I could not replicate:

create role sp_test;

alter role sp_test SET search_path TO "$user", public;

pg_dumpall -g -U postgres > sp_test.sql

In sp_test.sql

CREATE ROLE sp_test;

ALTER ROLE sp_test SET search_path TO "$user", public;

drop role sp_test ;

psql -d test -U postgres  -f sp_test.sql


\du

sp_test| Cannot login 
   | {}



\drds
 List of settings
  Role   | Database |  Settings
-+--+-
 sp_test |  | search_path="$user", public







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

Re: Multi client in subscription?


On 07/29/2018 11:53 PM, xOChilpili wrote:

Hi everyone,

I have 2 virtual machines, one that i use at home and the other one that 
i use at work, so, in the server i have one publication and for each 
client i have the same subscription, but every time i go out from home 
to work, i have to stop postgresql service in order to use subscription 
at work, and viceversa, is it possible to use both at the same time ?


This is going to need more information:

1) Not clear to me whether the server is one of the virtual machines or 
some other machine?


2) What is the publication definition?

3) How are the subscriptions set up?

4) Are the virtual machines on continuously?





Thanks a lot!


--
xOCh


--
PAranoids Group

218



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

Re: Postgresql 10.4 installation issues on Ubuntu 14.05


On 07/30/2018 12:40 AM, vardenis pavardenis wrote:


hello.
thats interesting.
maybe you have clue why it happened (i tried install by instructions) 
and how to fix it? :)


Purge the existing packages and try installing again.


thanks


HTH,
Robert





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

Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR: syntax error at or near "$" .

2018-07-30 Thread Achilleas Mantzios


On 30/07/2018 16:23, Adrian Klaver wrote:

On 07/30/2018 05:57 AM, Achilleas Mantzios wrote:

This is with PostgreSQL 10.4.
How to reproduce :
postgres@smadev:~% psql
psql (10.4)
Type "help" for help.

Alter the role for search path :
dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
ALTER ROLE
dynacom=#

Verify :
postgres@smadev:~% psql -U amura3
Password for user amura3:
psql (10.4)
Type "help" for help.

dynacom=> show search_path ;
 search_path
---
  $user, amuragents, public
(1 row)

dynacom=>

pg_dumpall's output :
ALTER ROLE amura3 SET search_path TO $user, amuragents, public;

psql -f pg_dumpall_out.sql :
dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
ERROR:  syntax error at or near "$"


Well the above does not have $user double quoted. Was that hand entered or was 
that like that in the pg_dumpall_out.sql file?



Is this a bug or am I missing something ?


I could not replicate:


You are absolutely right, I apologize for the noise :(
I had forgotten that we had to tweak src/backend/utils/misc/guc.c in order for 
search_path to work with pgbouncer in transaction mode.

--- /usr/local/src/postgresql-10.4/src/backend/utils/misc/guc.c.orig 2018-05-07 
23:51:40.0 +0300
+++ /usr/local/src/postgresql-10.4/src/backend/utils/misc/guc.c 2018-06-14 
16:07:29.832476000 +0300
@@ -3266,7 +3266,7 @@
    {"search_path", PGC_USERSET, CLIENT_CONN_STATEMENT,
    gettext_noop("Sets the schema search order for names that 
are not schema-qualified."),
    NULL,
-   GUC_LIST_INPUT | GUC_LIST_QUOTE
+   GUC_LIST_INPUT | GUC_REPORT
    },
    &namespace_search_path,
    "\"$user\", public",

With GUC_LIST_QUOTE it kept re-quoting and it broke the app. Without GUC_REPORT 
it was losing the search_path.



create role sp_test;

alter role sp_test SET search_path TO "$user", public;

pg_dumpall -g -U postgres > sp_test.sql

In sp_test.sql

CREATE ROLE sp_test;

ALTER ROLE sp_test SET search_path TO "$user", public;

drop role sp_test ;

psql -d test -U postgres  -f sp_test.sql


\du

sp_test    | Cannot login    | {}


\drds
 List of settings
  Role   | Database |  Settings
-+--+-
 sp_test |  | search_path="$user", public









--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Multi client in subscription?

2018-07-30 Thread Andreas Kretschmer

On 30 July 2018 15:29:27 CEST, Adrian Klaver  wrote:
>On 07/29/2018 11:53 PM, xOChilpili wrote:
>> Hi everyone,
>> 
>> I have 2 virtual machines, one that i use at home and the other one
>that 
>> i use at work, so, in the server i have one publication and for each 
>> client i have the same subscription, but every time i go out from
>home 
>> to work, i have to stop postgresql service in order to use
>subscription 
>> at work, and viceversa, is it possible to use both at the same time ?
>

Sounds like you are searching a multi-master solution. If yes, BDR.


Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR: syntax error at or near "$" .

Achilleas Mantzios  writes:
> This is with PostgreSQL 10.4.
> How to reproduce :

> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
> ALTER ROLE
> ...
> dynacom=> show search_path ;
>      search_path
> ---
>   $user, amuragents, public
> (1 row)

Hm, that's not happening for me:

regression=# alter user joe set search_path to "$user", public;
...
regression=> show search_path ;
   search_path   
-
 "$user", public
(1 row)

and then pg_dumpall produces

ALTER ROLE joe SET search_path TO "$user", public;

There was a relevant bug fix in March (commit 742869946) ...
are you certain this is a 10.4 server, and not 10.3 or older?

regards, tom lane

Re: Postgresql 10.4 installation issues on Ubuntu 14.05


On 07/30/2018 12:40 AM, vardenis pavardenis wrote:


hello.
thats interesting.
maybe you have clue why it happened (i tried install by instructions) 
and how to fix it? :)


Just dawned on me that you are running 14.05 not 14.04 and that the 
packages you installed are for 14.04.


Per here:

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

only the LTS versions are officially supported. You might want to try 
installing the 16.04 version on the theory it is more likely to be 
backwards compatible then the 14.04 being forward compatible.



thanks


HTH,
Robert





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

Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR: syntax error at or near "$" .

2018-07-30 Thread Achilleas Mantzios


On 30/07/2018 16:51, Tom Lane wrote:

Achilleas Mantzios  writes:

This is with PostgreSQL 10.4.
How to reproduce :
dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
ALTER ROLE
...
dynacom=> show search_path ;
      search_path
---
   $user, amuragents, public
(1 row)

Hm, that's not happening for me:

You (and Adrian) are right. This is due to our own tweaking (which I had 
forgotten). Sorry for the false alarm.


regression=# alter user joe set search_path to "$user", public;
...
regression=> show search_path ;
search_path
-
  "$user", public
(1 row)

and then pg_dumpall produces

ALTER ROLE joe SET search_path TO "$user", public;

There was a relevant bug fix in March (commit 742869946) ...
are you certain this is a 10.4 server, and not 10.3 or older?

regards, tom lane



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR: syntax error at or near "$" .

Achilleas Mantzios  writes:
> On 30/07/2018 16:51, Tom Lane wrote:
>> Hm, that's not happening for me:

> You (and Adrian) are right. This is due to our own tweaking (which I had 
> forgotten). Sorry for the false alarm.

It looks like your hack was to work around the bug that was fixed
properly in 742869946.  You should be able to undo that now ...

regards, tom lane

Restore relhaspkey in PostgreSQL Version 11 Beta

In the release notes for Version 11 Beta, under changes, I see these scary
remarks:

Remove relhaspkey column from system table pg_class (Peter Eisentraut)

Applications needing to check for a primary key should consult pg_index.

That absolutely breaks my code (and I'm guessing others), as I have a cron
job that checks for tables that were created with no pkey.
IE: SELECT n.nspname,
   c.relname as table,
   c.reltuples::bigint
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid =c.relnamespace )
 WHERE relkind = 'r' AND
   relname NOT LIKE 'pg_%' AND
   relname NOT LIKE 'sql_%' AND
   relhaspkey = FALSE
ORDER BY n.nspname, c.relname;

relhaspkey has been in pg_class since the earliest version of PostgreSQL.
AFAIK
there is NO NEED to remove it! In fact, the system catalogs should only be
changed when there is an absolute requirement, not at someone's whim.
Adding a column is fine, but dropping columns that breaks code is
ridiculous.

Please restore that column before the final release!

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

Melvin Davidson  writes:
> In the release notes for Version 11 Beta, under changes, I see these scary
> remarks:
> Remove relhaspkey column from system table pg_class (Peter Eisentraut)
> Applications needing to check for a primary key should consult pg_index.
> 
> That absolutely breaks my code (and I'm guessing others), as I have a cron
> job that checks for tables that were created with no pkey.

Well, I'd say your code was broken anyway, because it has never been the
case that relhaspkey meant that the table *currently* has a primary key.
We got rid of it on the grounds that its semantics were too squishy to
be useful.

What you want is something like

select relname from pg_class c where relkind = 'r' and
  not exists (select 1 from pg_index where indrelid = c.oid and indisprimary);

which will give the right answer in all PG versions.

regards, tom lane

Design of a database table

2018-07-30 Thread hmidi slim

I'm trying to design a database table. First of all there are two
alternatives:
1-) Divide the table into two tables and make a join.
2-) Design a single table.

1rst alternative:
Create table data_periods(
id serial primary key not null,
period daterange,
project_id integer
)

create table data_periods_info(
id serial primary key not null,
data_periods_id integer,
data_sub_periods daterange,
stock1 integer,
stock2 integer,
CONSTRAINT data_periods_allotment_id_fkey FOREIGN KEY (data_periods_id)
REFERENCES data_periods (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

The table data_periods contains 1M rows and data_periods_info 5M rows.
I added an index to the table data_periods_info for the column
data_periods_id
I execute this query:
select
data_periods.id,
data_sub_periods,
project_id,
stock1,
stock2
from data_periods
inner join data_periods_info on data_periods_info.data_periods_id =
data_periods.id
where data_periods.period && '[2018-07-28, 2018-08-02]'::daterange
and data_sub_periods  && '[2018-07-28, 2018-08-02]'::daterange

I got an execution time of : 1s 300ms


2nd alternative:
create table data_periods_second(
id serial primary key not null,
data_sub_periods daterange,
project_id integer,
stock1 integer,
stock2 integer)

I run this query;
select * from data_periods_second
where data_sub_periods && '[2018-07-28, 2018-08-02]'::daterange

I got such a execution time : 1s


Is it normal to get an execution time when using join relation greatest
than the execution time of a table contains million of rows and many
columns?

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On Mon, Jul 30, 2018 at 10:31 AM, Tom Lane  wrote:

> Melvin Davidson  writes:
> > In the release notes for Version 11 Beta, under changes, I see these
> scary
> > remarks:
> > Remove relhaspkey column from system table pg_class (Peter Eisentraut)
> > Applications needing to check for a primary key should consult pg_index.
> >
> > That absolutely breaks my code (and I'm guessing others), as I have a
> cron
> > job that checks for tables that were created with no pkey.
>
> Well, I'd say your code was broken anyway, because it has never been the
> case that relhaspkey meant that the table *currently* has a primary key.
> We got rid of it on the grounds that its semantics were too squishy to
> be useful.
>
> What you want is something like
>
> select relname from pg_class c where relkind = 'r' and
>   not exists (select 1 from pg_index where indrelid = c.oid and
> indisprimary);
>
> which will give the right answer in all PG versions.
>
> regards, tom lane
>

it has never been the
case that relhaspkey meant that the table *currently* has a primary key.

Tom,

* >it has never been the case that relhaspkey meant that the table
*currently* has a primary key. *

*That is a poor excuse, because that is exactly what I am looking for!*

* squishy semantics or not, dropping columns from system catalogs is
ridiculous. *

*It appears to me that the developers are going rogue. Why should I, and
others, *
*I have to change my code ( which absolutely works ), simply because the
developers*
*feel it's ok to drop columns from system catalogs based on semantics?*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Design of a database table

2018-07-30 Thread Ron


On 07/30/2018 09:37 AM, hmidi slim wrote:
I'm trying to design a database table. First of all there are two 
alternatives:

1-) Divide the table into two tables and make a join.
2-) Design a single table.

1rst alternative:
Create table data_periods(
id serial primary key not null,
period daterange,
project_id integer
)

create table data_periods_info(
id serial primary key not null,
data_periods_id integer,
data_sub_periods daterange,
stock1 integer,
stock2 integer,
CONSTRAINT data_periods_allotment_id_fkey FOREIGN KEY (data_periods_id)
    REFERENCES data_periods (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)


Are you absolutely 100% positive that there will NEVER be more than two 
stock numbers?  (People say "yes" to this kind of question all the time and 
then discover that they need more stock numbers when the business changes.)


--
Angular momentum makes the world go 'round.

jndi jdbc url with ssl authenticat in tomcat ... fails org.xml.sax.SAXParseException columnNumber: 79; The reference to entity "ssl" must end with the ';' delimiter

2018-07-30 Thread Didier Wiroth

Hello,
I'm trying to configure a postgres jndi resource in tomcat that needs to 
connect via ssl and client certificate for authentication.
Unfortunately ... without success.

The user is: esrde_aoo and authenticates with a certificate (no password!).

Here is the tomcat 8.5.31 (running on jre 1.8.0_152-b16) resource configuration:
  

I tried many many different combination ... it always fails at the second 
parameter.
org.apache.tomcat.util.digester.Digester.fatalError Parse Fatal Error at line 
21 column 79: The reference to entity "ssl" must end with the ';' delimiter.
 org.xml.sax.SAXParseException; systemId: 
file:/D:/apps/web-data/tam/conf/db-context-ora12.xml; lineNumber: 21; 
columnNumber: 79; The reference to entity "ssl" must end with the ';' delimiter.
at 
com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:203)
at 
com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.fatalError(ErrorHandlerWrapper.java:177)
at 
com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:400)
at 
com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:327)
at 
com.sun.org.apache.xerces.internal.impl.XMLScanner.reportFatalError(XMLScanner.java:1472)
at 
com.sun.org.apache.xerces.internal.impl.XMLScanner.scanAttributeValue(XMLScanner.java:913)
at 
com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanAttribute(XMLDocumentFragmentScannerImpl.java:1548)
at 
com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanStartElement(XMLDocumentFragmentScannerImpl.java:1315)
at 
com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2784)
at 
com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:602)
at 
com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:505)
at 
com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:841)
at 
com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:770)
at 
com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
at 
com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1213)
at 
com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(SAXParserImpl.java:643)
at org.apache.tomcat.util.digester.Digester.parse(Digester.java:1521)
at 
org.apache.catalina.startup.ContextConfig.processContextConfig(ContextConfig.java:527)
at 
org.apache.catalina.startup.ContextConfig.contextConfig(ContextConfig.java:465)
at 
org.apache.catalina.startup.ContextConfig.init(ContextConfig.java:728)
at 
org.apache.catalina.startup.ContextConfig.lifecycleEvent(ContextConfig.java:310)
at 
org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:94)
at 
org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:395)
at org.apache.catalina.util.LifecycleBase.init(LifecycleBase.java:108)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:140)
at 
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:754)
at 
org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:730)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:734)
at 
org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1140)
at 
org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1875)
at 
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

I would really appreciate some help.

Is my connection String correct?
Is this a tomcat or java bug?

Thank you very much any hint/help!
Regards,
Didier

Re: Incorrect description of the WITH CHECK in the row security can lead to the security issue

=?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?=  writes:
> PostgreSQL 10 (in 11 the same
> https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html
> cite
> To use a different policy for rows that are being added to the table
> compared to those rows that are visible, the WITH CHECK clause can be used.
> This policy would allow all users to view all rows in the users table, but
> only modify their own:

> CREATE POLICY user_policy ON users
> USING (true)
> WITH CHECK (user_name = current_user);
> end cite

> This is is wrong description. Every one can steal other row with such
> policy.

Yup, you're right, this is too simple.

> The right statement to not allow modify rows by other user will be

> CREATE POLICY user_policy ON users
> USING (user_name = current_user)
> WITH CHECK (user_name = current_user);

Well, that also hides the other users' rows, which is not what the
example claims to do.  To make it work as documented, we need something
like

CREATE POLICY user_sel_policy ON users
FOR SELECT
USING (true);
CREATE POLICY user_mod_policy ON users
USING (user_name = current_user);

I've pushed a patch along that line.  Thanks for the report!

regards, tom lane

alter table docs

2018-07-30 Thread Rob Sargent

I was just looking up alter table add constraint syntax under 
"current(10)" and we get


    ADD /table_constraint/ [ NOT VALID ]
    ADD /table_constraint_using_index/

There is a description below for the using_index version but none for 
the plain version.  There is a block for the plain version on the CREATE 
TABLE page. Should it not also appear in the ALTER TABLE page?

Re: Postgresql 10.4 installation issues on Ubuntu 14.05

2018-07-30 Thread vardenis pavardenis

2018-07-30 16:51 GMT+03:00 Adrian Klaver :

> On 07/30/2018 12:40 AM, vardenis pavardenis wrote:
>
> hello.
>> thats interesting.
>> maybe you have clue why it happened (i tried install by instructions) and
>> how to fix it? :)
>>
>
> Just dawned on me that you are running 14.05 not 14.04 and that the
> packages you installed are for 14.04.
>

nice shot!
Will try it.
Thanks.


>
> Per here:
>
> https://www.postgresql.org/download/linux/ubuntu/
>
> only the LTS versions are officially supported. You might want to try
> installing the 16.04 version on the theory it is more likely to be
> backwards compatible then the 14.04 being forward compatible.
>
>
> thanks
>>
>>
>> HTH,
>> Robert
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Postgresql 10.4 installation issues on Ubuntu 14.05

2018-07-30 Thread vardenis pavardenis

2018-07-30 16:39 GMT+03:00 Adrian Klaver :

> On 07/30/2018 12:40 AM, vardenis pavardenis wrote:
>
> hello.
>> thats interesting.
>> maybe you have clue why it happened (i tried install by instructions) and
>> how to fix it? :)
>>
>
> Purge the existing packages and try installing again.
>

Did it already few times per few weeks. And google didn't help :)
that's because i am here. (i even try installer from here:
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, but
with the same result - server can't start.)
Anyway thanks for suggestion.

>
> thanks
>>
>>
>> HTH,
>> Robert
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: logical replication snapshots

2018-07-30 Thread Dimitri Maziuk

On 07/27/2018 05:20 PM, Adrian Klaver wrote:
> On 07/27/2018 03:04 PM, Dimitri Maziuk wrote:

>> ... but if I did the publication for ALL, I could just use streaming
>> replication ...
> 
> Well I was just showing the extremes from a single table publication to
> ALL tables. You can also do subsets of ALL. Remember that binary
> replication(streaming) involves the whole Postgres cluster, e.g. all the
> databases in the cluster no choice in the matter. Also it does not allow
> you to shape what is replicated.
Yes, and that's why I was looking into logical replication. Now that I
looked, it seems the limitations are too many for our use case now, and
that spinning up a separate "cluster" for each "replicated dataset" is
simply less work ATM.

Thanks again,
-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Strange behavior with missing column in SQL function

2018-07-30 Thread Marcelo Lacerda

Here's the code that reproduces the behavior:
http://paste.debian.net/1035412/

I have already discussed this in the IRC channel but there doesn't seem to
be a consensus on whether this is a bug here's a brief transcript of
RhodiumToad's opinion:

> this isn't new, goes back to 9.1 at least
> basically, the error path in sql_fn_post_column_ref is a bit confused.
> seeing r.c it tries to resolve it as parameter.field, fails, and rather
than reporting the error directly as being a missing field, it just returns
with the reference unresolved
>then the outer parser code, having failed to resolve it as table.column
and having had the hook function not override it, reports it on the
assumption that it's a missing table
> so it's probably been this way for as long as named parameters have
worked in language sql
> msl09: as far as I can tell it's just giving the wrong error in an error
path, everything that's supposed to work does work
> msl09: but the error is definitely misleading

My question is "Is this a bug? Should it be reported?"

Re: Postgresql 10.4 installation issues on Ubuntu 14.05


On 07/30/2018 10:33 AM, vardenis pavardenis wrote:



2018-07-30 16:51 GMT+03:00 Adrian Klaver >:


On 07/30/2018 12:40 AM, vardenis pavardenis wrote:

hello.
thats interesting.
maybe you have clue why it happened (i tried install by
instructions) and how to fix it? :)


Just dawned on me that you are running 14.05 not 14.04 and that the
packages you installed are for 14.04.


nice shot!
Will try it.


If that does no work you can always install from source:

https://www.postgresql.org/ftp/source/v10.4/

That will require installing dev packages though.


Thanks.




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

Re: alter table docs


On 07/30/2018 09:57 AM, Rob Sargent wrote:
I was just looking up alter table add constraint syntax under 
"current(10)" and we get


     ADD /table_constraint/ [ NOT VALID ]
     ADD /table_constraint_using_index/

There is a description below for the using_index version but none for 
the plain version.  There is a block for the plain version on the CREATE 


I see one:

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

"ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as 
CREATE TABLE, plus the option NOT VALID, which is currently only allowed 
for foreign key and CHECK constraints. If the constraint is marked NOT 
VALID, the potentially-lengthy initial check to verify that all rows in 
the table satisfy the constraint is skipped. The constraint will still 
be enforced against subsequent inserts or updates (that is, they'll fail 
unless there is a matching row in the referenced table, in the case of 
foreign keys; and they'll fail unless the new row matches the specified 
check constraints). But the database will not assume that the constraint 
holds for all rows in the table, until it is validated by using the 
VALIDATE CONSTRAINT option.

"


TABLE page. Should it not also appear in the ALTER TABLE page?





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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On 07/30/2018 07:42 AM, Melvin Davidson wrote:

On Mon, Jul 30, 2018 at 10:31 AM, Tom Lane > wrote:

Melvin Davidson mailto:melvin6...@gmail.com>>
writes:
> In the release notes for Version 11 Beta, under changes, I see these scary
> remarks:
> Remove relhaspkey column from system table pg_class (Peter Eisentraut)
> Applications needing to check for a primary key should consult pg_index.
>
> That absolutely breaks my code (and I'm guessing others), as I have a cron

> job that checks for tables that were created with no pkey.

Well, I'd say your code was broken anyway, because it has never been the
case that relhaspkey meant that the table *currently* has a primary key.
We got rid of it on the grounds that its semantics were too squishy to
be useful.

What you want is something like

select relname from pg_class c where relkind = 'r' and
not exists (select 1 from pg_index where indrelid = c.oid and
indisprimary);

which will give the right answer in all PG versions.

regards, tom lane

it has never been the
case that relhaspkey meant that the table *currently* has a primary key.

Tom,
*
*
*>it has never been the case that relhaspkey meant that the table
*currently* has a primary key.

*
*
*
*That is a poor excuse, because that is exactly what I am looking for!*
*squishy semantics or not, dropping columns from system catalogs is
ridiculous.

*
*It appears to me that the developers are going rogue. Why should I, and
others,

*
*I have to change my code ( which absolutely works ), simply because the
developers*

*feel it's ok to drop columns from system catalogs based on semantics?*

Use the information_schema then:

https://www.postgresql.org/docs/10/static/infoschema-table-constraints.html

The system catalogs are going to change over time by addition and/or
subtraction. That is a fact of life.

If you are interested in the reasons for the change then:

https://www.postgresql.org/message-id/flat/b1a24c6c-6913-f89c-674e-0704f0ed6...@2ndquadrant.com

--
*Melvin Davidson**

Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!

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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

* >it has never been the case that relhaspkey meant that the table
*currently* has a primary key. *







*Hmmm, I guess it's a lot harder to fix "squishy semantics"from  "True
if the table has (or once had) a primary key"  to"True if the table has
a primary key after vacuum"rather than just dropping a column that has
existed from version 7.2.So now I guess the policy is break code instead of
fix documention.That meakes sense...NOT!*


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: alter table docs

2018-07-30 Thread Rob Sargent



On 07/30/2018 03:07 PM, Adrian Klaver wrote:

On 07/30/2018 09:57 AM, Rob Sargent wrote:
I was just looking up alter table add constraint syntax under 
"current(10)" and we get


     ADD /table_constraint/ [ NOT VALID ]
     ADD /table_constraint_using_index/

There is a description below for the using_index version but none for 
the plain version.  There is a block for the plain version on the CREATE 


I see one:

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

"ADD table_constraint [ NOT VALID ]

    This form adds a new constraint to a table using the same syntax 
as CREATE TABLE, plus the option NOT VALID, which is currently only 
allowed for foreign key and CHECK constraints. If the constraint is 
marked NOT VALID, the potentially-lengthy initial check to verify that 
all rows in the table satisfy the constraint is skipped. The 
constraint will still be enforced against subsequent inserts or 
updates (that is, they'll fail unless there is a matching row in the 
referenced table, in the case of foreign keys; and they'll fail unless 
the new row matches the specified check constraints). But the database 
will not assume that the constraint holds for all rows in the table, 
until it is validated by using the VALIDATE CONSTRAINT option.

"


TABLE page. Should it not also appear in the ALTER TABLE page?




OK, I was expecting a block in the enclosing text-area for this simple 
form of the command similar to the one for the /using_index/ form.  I 
suppose the existence of the latter lead me to expect the former.  If 
it's as intended I'm fine with that.

Cosmetically-varying casts added to view definitions

2018-07-30 Thread Ken Tanzer

Hi.  As background/context, I'm working on a script to take a series of
databases and make them timezone-aware.  This basically involves saving all
the view definitions, dropping all the views, changing all the timestamp
columns without time zones to TS with TZ, and then recreating all the
views.  As a sanity check on all of this, I compared the resulting view
definitions to what existed before starting.  Most of them were equivalent,
with a few exceptions.

These exceptions all seem to be where slightly different casting is used
when the view definition gets created.  For example, starting with this
view (payment_form_code is a varchar(20):

ag_reach=> CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE
payment_form_code IN ('CREDIT_CARD','OTHER'));

CREATE VIEW
ag_reach=> \d+ test_tmp
 View "public.test_tmp"
  Column  |  Type   | Modifiers | Storage | Description
--+-+---+-+-
 ?column? | integer |   | plain   |
View definition:
 SELECT 1
   FROM l_payment_form
  WHERE l_payment_form.payment_form_code::text = ANY
(ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character
varying]::text[]);

I wasn't very surprised by that.  But when I take the view definition as
stored in PG above, and create a new view:

ag_reach=> CREATE VIEW test_tmp2 AS SELECT 1 FROM l_payment_form WHERE
l_payment_form.payment_form_code::text = ANY

(ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);

CREATE VIEW

ag_reach=> \d+ test_tmp2
View "public.test_tmp2"
  Column  |  Type   | Modifiers | Storage | Description
--+-+---+-+-
 ?column? | integer |   | plain   |
View definition:
 SELECT 1
   FROM l_payment_form
  WHERE l_payment_form.payment_form_code::text = ANY
(ARRAY['CREDIT_CARD'::character varying::text, 'OTHER'::character
varying::text]);

 you'll see it now casts the individual array elements to text, rather than
the whole array as in test_tmp.

This doesn't impair the view's functionality, so I can't necessarily
complain.  But it does make it harder for me to know if the views were
recreated correctly.  I'd be curious to know what is going on here, and if
there's any way to avoid this behavior.

Thanks!

Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: alter table docs


On 07/30/2018 02:24 PM, Rob Sargent wrote:


On 07/30/2018 03:07 PM, Adrian Klaver wrote:

On 07/30/2018 09:57 AM, Rob Sargent wrote:
I was just looking up alter table add constraint syntax under 
"current(10)" and we get


     ADD /table_constraint/ [ NOT VALID ]
     ADD /table_constraint_using_index/

There is a description below for the using_index version but none for 
the plain version.  There is a block for the plain version on the CREATE 


I see one:

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

"ADD table_constraint [ NOT VALID ]

    This form adds a new constraint to a table using the same syntax 
as CREATE TABLE, plus the option NOT VALID, which is currently only 
allowed for foreign key and CHECK constraints. If the constraint is 
marked NOT VALID, the potentially-lengthy initial check to verify that 
all rows in the table satisfy the constraint is skipped. The 
constraint will still be enforced against subsequent inserts or 
updates (that is, they'll fail unless there is a matching row in the 
referenced table, in the case of foreign keys; and they'll fail unless 
the new row matches the specified check constraints). But the database 
will not assume that the constraint holds for all rows in the table, 
until it is validated by using the VALIDATE CONSTRAINT option.

"


TABLE page. Should it not also appear in the ALTER TABLE page?




OK, I was expecting a block in the enclosing text-area for this simple 
form of the command similar to the one for the /using_index/ form.  I 
suppose the existence of the latter lead me to expect the former.  If 
it's as intended I'm fine with that.


Aah I see, you where referring to:

"and table_constraint_using_index is:

[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY 
IMMEDIATE ]

"












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

Re: alter table docs

2018-07-30 Thread Rob Sargent





OK, I was expecting a block in the enclosing text-area for this 
simple form of the command similar to the one for the /using_index/ 
form.  I suppose the existence of the latter lead me to expect the 
former.  If it's as intended I'm fine with that.


Aah I see, you where referring to:

"and table_constraint_using_index is:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY 
IMMEDIATE ]

"




Exactly.  That that is in the "box" made me think a similar blurb for 
the non-index version should be there also.

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

2018-07-30 Thread Andres Freund

Hi,

On 2018-07-30 17:21:25 -0400, Melvin Davidson wrote:
> * >it has never been the case that relhaspkey meant that the table
> *currently* has a primary key. *

> *Hmmm, I guess it's a lot harder to fix "squishy semantics"from  "True
> if the table has (or once had) a primary key"  to"True if the table has
> a primary key after vacuum"rather than just dropping a column that has
> existed from version 7.2.So now I guess the policy is break code instead of
> fix documention.That meakes sense...NOT!*

A large portion of the system catalogs (i.e. objects within
pg_catalog.*) are essentially internal implementation details and we'll
change them if it makes our live easier. If you want stability use
information_schema which we'll try very hard to not ever break.  Keeping
random atavistic things around, would slow us down, which will be a
price everybody is paying.

Greetings,

Andres Freund

Re: alter table docs

Rob Sargent  writes:
> Exactly.  That that is in the "box" made me think a similar blurb for 
> the non-index version should be there also.

This seems to have been fixed in v11 but not back-patched.

regards, tom lane

Re: Cosmetically-varying casts added to view definitions


On 07/30/2018 02:26 PM, Ken Tanzer wrote:
Hi.  As background/context, I'm working on a script to take a series of 
databases and make them timezone-aware.  This basically involves saving 
all the view definitions, dropping all the views, changing all the 
timestamp columns without time zones to TS with TZ, and then recreating 
all the views.  As a sanity check on all of this, I compared the 
resulting view definitions to what existed before starting.  Most of 
them were equivalent, with a few exceptions.


These exceptions all seem to be where slightly different casting is used 
when the view definition gets created.  For example, starting with this 
view (payment_form_code is a varchar(20):


ag_reach=> CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE 
payment_form_code IN ('CREDIT_CARD','OTHER'));


CREATE VIEW
ag_reach=> \d+ test_tmp
  View "public.test_tmp"
   Column  |  Type   | Modifiers | Storage | Description
--+-+---+-+-
  ?column? | integer |   | plain   |
View definition:
  SELECT 1
FROM l_payment_form
   WHERE l_payment_form.payment_form_code::text = ANY 
(ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);

I wasn't very surprised by that.  But when I take the view definition as 
stored in PG above, and create a new view:


ag_reach=> CREATE VIEW test_tmp2 AS SELECT 1 FROM l_payment_form WHERE 
l_payment_form.payment_form_code::text = ANY


(ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);

CREATE VIEW

ag_reach=> \d+ test_tmp2
 View "public.test_tmp2"
   Column  |  Type   | Modifiers | Storage | Description
--+-+---+-+-
  ?column? | integer |   | plain   |
View definition:
  SELECT 1
FROM l_payment_form
   WHERE l_payment_form.payment_form_code::text = ANY 
(ARRAY['CREDIT_CARD'::character varying::text, 'OTHER'::character 
varying::text]);

  you'll see it now casts the individual array elements to text, rather 
than the whole array as in test_tmp.


Which is where it stops from my test:

CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE season IN 
('annual', 'perennial'));


View "public.cp_view"
  Column  |  Type   | Collation | Nullable | Default | Storage | 
Description

--+-+---+--+-+-+-
 ?column? | integer |   |  | | plain   |
View definition:
 SELECT 1
   FROM cell_per
  WHERE cell_per.season::text = ANY (ARRAY['annual'::character varying, 
'perennial'::character varying]::text[]);


CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE 
cell_per.season::text = ANY (ARRAY['annual'::character varying, 
'perennial'::character varying]::text[]));


View "public.cp_view"
  Column  |  Type   | Collation | Nullable | Default | Storage | 
Description

--+-+---+--+-+-+-
 ?column? | integer |   |  | | plain   |
View definition:
 SELECT 1
   FROM cell_per
  WHERE cell_per.season::text = ANY (ARRAY['annual'::character 
varying::text, 'perennial'::character varying::text]);



CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE 
cell_per.season::text = ANY (ARRAY['annual'::character varying::text, 
'perennial'::character varying::text]));


View "public.cp_view"
  Column  |  Type   | Collation | Nullable | Default | Storage | 
Description

--+-+---+--+-+-+-
 ?column? | integer |   |  | | plain   |
View definition:
 SELECT 1
   FROM cell_per
  WHERE cell_per.season::text = ANY (ARRAY['annual'::character 
varying::text, 'perennial'::character varying::text]);


Seems to be simplifying down to an end point.



This doesn't impair the view's functionality, so I can't necessarily 
complain.  But it does make it harder for me to know if the views were 
recreated correctly.  I'd be curious to know what is going on here, and 
if there's any way to avoid this behavior.


The only way I can think of to maintain a consistent definition is to 
always create the view from the original definition:


CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE 
payment_form_code IN ('CREDIT_CARD','OTHER'));




Thanks!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On Mon, Jul 30, 2018 at 6:21 PM, Andres Freund  wrote:

> Hi,
>
> On 2018-07-30 17:21:25 -0400, Melvin Davidson wrote:
> > * >it has never been the case that relhaspkey meant that the table
> > *currently* has a primary key. *
>
> > *Hmmm, I guess it's a lot harder to fix "squishy semantics"from
> "True
> > if the table has (or once had) a primary key"  to"True if the table
> has
> > a primary key after vacuum"rather than just dropping a column that has
> > existed from version 7.2.So now I guess the policy is break code
> instead of
> > fix documention.That meakes sense...NOT!*
>
> A large portion of the system catalogs (i.e. objects within
> pg_catalog.*) are essentially internal implementation details and we'll
> change them if it makes our live easier. If you want stability use
> information_schema which we'll try very hard to not ever break.  Keeping
> random atavistic things around, would slow us down, which will be a
> price everybody is paying.
>
> Greetings,
>
> Andres Freund
>

*> If you want stability use information_schema which we'll try very hard
to not ever break.  *

*Of course. Would you be so kind as to point out where in the
information_schema  it *
*indicates if a table has a primary key or not. Oh wait, now I
remember...no place.*

*>Keeping random atavistic things around, would slow us down, which will be
a>price everybody is paying. *
*Random atavistic things? I hardly think relhaspkey is random. It's been
there since version 7.2.*

*Exactly how does keeping it around slow you/us down?*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On 07/30/2018 04:11 PM, Melvin Davidson wrote:

On Mon, Jul 30, 2018 at 6:21 PM, Andres Freund > wrote:

Hi,

On 2018-07-30 17:21:25 -0400, Melvin Davidson wrote:
 > * >it has never been the case that relhaspkey meant that the table
 > *currently* has a primary key. *

 > *Hmmm, I guess it's a lot harder to fix "squishy semantics"from 
     "True

> if the table has (or once had) a primary key"  to    "True if the table 
has
> a primary key after vacuum"rather than just dropping a column that has
 > existed from version 7.2.So  now I guess the
policy is break code instead of
 > fix documention.That meakes sense...NOT!*

A large portion of the system catalogs (i.e. objects within
pg_catalog.*) are essentially internal implementation details and we'll
change them if it makes our live easier. If you want stability use
information_schema which we'll try very hard to not ever break.  Keeping
random atavistic things around, would slow us down, which will be a
price everybody is paying.

Greetings,

Andres Freund

*> If you want stability use information_schema which we'll try very 
hard to not ever break.

*
*Of course. Would you be so kind as to point out where in the 
information_schema it

*
*indicates if a table has a primary key or not. Oh wait, now I 
remember...no place.*

https://www.postgresql.org/docs/10/static/infoschema-table-constraints.html

"constraint_type 	character_data 	Type of the constraint: CHECK, FOREIGN 
KEY, PRIMARY KEY, or UNIQUE"

*
*
*>Keeping random atavistic things around, would slow us down, which will 
be a

 >price everybody is paying.
*
*Random atavistic things? I hardly think relhaspkey is random. It's been 
there since version 7.2.*

*Exactly how does keeping it around slow you/us down?
*

--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!

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

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

2018-07-30 Thread Andres Freund

Hi,

On 2018-07-30 19:11:34 -0400, Melvin Davidson wrote:
> *Of course. Would you be so kind as to point out where in the
> information_schema  it *
> *indicates if a table has a primary key or not. Oh wait, now I
> remember...no place.*

As Adrian pointed out, that's wrong. It's in information_schema.  You're
pretty damn antagonistic while asking for things.

> *>Keeping random atavistic things around, would slow us down, which will be
> a>price everybody is paying. *

> *Random atavistic things? I hardly think relhaspkey is random. It's been
> there since version 7.2.*
> *Exactly how does keeping it around slow you/us down?*

Being old doesn't imply it's not superfluous and/or slows us
down. There've been a number of discussions and bug reports about the
inaccuracy - even though it's documented! - it in the last few
years. That alone costs time. Additionally it's code we need to
maintain.

Greetings,

Andres Freund

Re: Design of a database table


On 07/30/2018 07:37 AM, hmidi slim wrote:
I'm trying to design a database table. First of all there are two 
alternatives:

1-) Divide the table into two tables and make a join.
2-) Design a single table.

1rst alternative:
Create table data_periods(
id serial primary key not null,
period daterange,
project_id integer
)

create table data_periods_info(
id serial primary key not null,
data_periods_id integer,
data_sub_periods daterange,
stock1 integer,
stock2 integer,
CONSTRAINT data_periods_allotment_id_fkey FOREIGN KEY (data_periods_id)
     REFERENCES data_periods (id) MATCH SIMPLE
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
)

The table data_periods contains 1M rows and data_periods_info 5M rows.
I added an index to the table data_periods_info for the column 
data_periods_id

I execute this query:
select
data_periods.id ,
data_sub_periods,
project_id,
stock1,
stock2
from data_periods
inner join data_periods_info on data_periods_info.data_periods_id = 
data_periods.id 

where data_periods.period && '[2018-07-28, 2018-08-02]'::daterange
and data_sub_periods  && '[2018-07-28, 2018-08-02]'::daterange

I got an execution time of : 1s 300ms


2nd alternative:
create table data_periods_second(
id serial primary key not null,
data_sub_periods daterange,
project_id integer,
stock1 integer,
stock2 integer)

I run this query;
select * from data_periods_second
where data_sub_periods && '[2018-07-28, 2018-08-02]'::daterange

I got such a execution time : 1s


Is it normal to get an execution time when using join relation greatest 
than the execution time of a table contains million of rows and many 
columns?


Not surprising given that you are searching for a date range in two 
tables in the join versus only one in the other case. That fact that you 
are using the same range end points for period in data_periods and 
data_sub_periods in data_periods_info, to me, points to a design flaw. 
If period and data_sub_periods are the same why separate and repeat 
them? Also when asking for input on query planning/outcomes running 
EXPLAIN ANALYZE on the queries and posting the results here will help 
arrive at answer.



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

Re: Cosmetically-varying casts added to view definitions

2018-07-30 Thread Ken Tanzer

On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver 
wrote:

> On 07/30/2018 02:26 PM, Ken Tanzer wrote:
>
> > This doesn't impair the view's functionality, so I can't necessarily
> > complain.  But it does make it harder for me to know if the views were
> > recreated correctly.  I'd be curious to know what is going on here, and
> > if there's any way to avoid this behavior.
>
> The only way I can think of to maintain a consistent definition is to
> always create the view from the original definition:
>
> CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE
> payment_form_code IN ('CREDIT_CARD','OTHER'));
>

Thanks Adrian.  I do have what are supposed to be the original view
definitions, but I'm less than 100% confident they are accurate and
up-to-date, which is why I thought to use the actual definitions as
stored.  Might have to rethink that one though, or just take a leap of
faith that the views will functionally be the same!

Cheers,
Ken

p.s.,  I forgot to mention this in my original post, but for the record, PG
version 9.6.9.

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

2018-07-30 Thread David G. Johnston

On Mon, Jul 30, 2018 at 4:11 PM, Melvin Davidson 
wrote:

> *Random atavistic things? I hardly think relhaspkey is random. It's been
> there since version 7.2.*
> *Exactly how does keeping it around slow you/us down?*
>

My recap of the discussion thread:
That this has been around for a long time is not new information that was
unknown at the time the decision was made.  It was made in spite of that
piece of evidence.  It was decided that the leaving a "foot-gun" around for
new people to use was a problem worthy of solving. It this situation I find
the decision (given that no one is willing to make the field work as named)
to be the correct one (by a not-wide margin).

David J.

Re: Cosmetically-varying casts added to view definitions


On 07/30/2018 04:29 PM, Ken Tanzer wrote:
On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver 

Thanks Adrian.  I do have what are supposed to be the original view 
definitions, but I'm less than 100% confident they are accurate and 
up-to-date, which is why I thought to use the actual definitions as 
stored.  Might have to rethink that one though, or just take a leap of 
faith that the views will functionally be the same!


Or write a test or tests to verify they are the same.



Cheers,
Ken

p.s.,  I forgot to mention this in my original post, but for the record, 
PG version 9.6.9.

--



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

Re: Cosmetically-varying casts added to view definitions

2018-07-30 Thread Ken Tanzer

On Mon, Jul 30, 2018 at 4:52 PM Adrian Klaver 
wrote:

> On 07/30/2018 04:29 PM, Ken Tanzer wrote:
> > On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver 
> > Thanks Adrian.  I do have what are supposed to be the original view
> > definitions, but I'm less than 100% confident they are accurate and
> > up-to-date, which is why I thought to use the actual definitions as
> > stored.  Might have to rethink that one though, or just take a leap of
> > faith that the views will functionally be the same!
>
> Or write a test or tests to verify they are the same.
>

How would you do that exactly?  I can see verifying that they return the
same rows given existing data, but not that this would be true with
different data.

Did you mean comparing the returned results, writing some view-specific
tests on a case-by-case basis, or something way way better that I'm not
grasping?

Thanks!

Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Cosmetically-varying casts added to view definitions