Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

[David G. Johnston]

On Wednesday, November 8, 2017, Igal @ Lucee.org  wrote:
>
> Kettle throws an error though:  column "discount" is of type money but
> expression is of type double precision.
>
> The value in the offending insert is:  0.0
>
> Why does Postgres decide that 0.0 is "double precision" (which is a weird
> name in my opinion -- why can't it just be double) and not money?
>
The lack of quotes surrounding the value is significant.  Money input
requires a string literal.  Only (more or less) integer and double literal
values can be written without the single quotes.

David J.

Re: [GENERAL] Naming conventions for column names

[David G. Johnston]

On Mon, Nov 6, 2017 at 10:30 PM, Sachin Kotwal  wrote:

>
> Please committers give their final view on this.
>
>
​They, and others, have - its a "don't want".​

IOW, don't expend any effort since that effort will have been wasted - not
that it would take zero effort to accomplish.

If there is an addition to the system catalogs overview page of the docs
that describes the naming convention I think that would be swell.

David J.

Re: [GENERAL] idle in transaction, why

[David G. Johnston]

On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent  wrote:

> Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and
> this JDBC driver postgresql:42.1.4
>
>
> The postgresql.conf file has
>
> #idle_in_transaction_session_timeout = 0# in milliseconds, 0 is
> disabled
>
>
​There are numerous places where default settings can be configured.

https://www.postgresql.org/docs/10/static/config-setting.html

You should probably login as your application user and do "show
idle_in_transaction_session_timeout" to see what a clean session has for a
value and then figure out from there where that value is coming from.

David J.

Re: [GENERAL] ERROR: invalid input syntax for integer: "INSERT"

[David G. Johnston]

On Saturday, November 4, 2017, Robert Lakes  wrote:
>
> Here's the error I am receiving - when I am attempting to insert a record
> into a table:
>
> ERROR:  invalid input syntax for integer: "INSERT"
> LINE 1: ...T INTO listings_cdc SELECT statement_timestamp(), 'INSERT', ...
>
>

The second column of listings_cdc has a type of integer.  The word INSERT
is not a valid value to store there.

David J.

Re: [GENERAL] explain analyze output: 0 rows, 1M loops

[David G. Johnston]

On Wed, Nov 1, 2017 at 12:25 PM, Justin Pryzby <pry...@telsasoft.com> wrote:

> On Wed, Nov 01, 2017 at 12:19:21PM -0700, David G. Johnston wrote:
> > On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe <scott.marl...@gmail.com>
> > wrote:
> >
> > > So some of my output from an explain analyze here has a line that says
> > > this:
> > >
> > > ex Scan using warranty_order_item_warranty_order_id_idx on
> > > warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual
> > > time=0.110..0.111 rows=0 loops=1,010,844)
> > >
> >
> > Not my strong suit but, I'm pretty sure that reads: "The index was
> queried
> > 1M+ times and none of those inqueries resulted in a record being found".
> > IIUC I'd be wondering why some form of hash join wasn't used...
>
> Except that:
>
> https://www.postgresql.org/docs/current/static/using-explain.html
> "... the loops value reports the total number of executions of the node,
> and
> the actual time and ROWS VALUES SHOWN ARE AVERAGES PER-EXECUTION."
>

I seem to recall a somewhat recent commit that dealt with this.  The
problem is that with 1M loops a small number of rows returned will be
indistinguishable from zero when computed as an average within finite
precision.

Seeing entire plans, and not just a single line of one, tends to help too.

David J.

Re: [GENERAL] explain analyze output: 0 rows, 1M loopa

[David G. Johnston]

On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe 
wrote:

> So some of my output from an explain analyze here has a line that says
> this:
>
> ex Scan using warranty_order_item_warranty_order_id_idx on
> warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual
> time=0.110..0.111 rows=0 loops=1,010,844)
>

​Not my strong suit but, I'm pretty sure that reads: "The index was queried
1M+ times and none of those inqueries resulted in a record being found".
IIUC I'd be wondering why some form of hash join wasn't used...

David J.​

[GENERAL] From the "SQL is verbose" department, WINDOW RANGE specifications

[David G. Johnston]

The default range specification is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

It seems like a common second choice is to want:

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Why did they have to make something so common take 49 characters that, for
seldom-using users, is nearly impossible to remember?

The following would seem to suffice:

{ RANGE | ROWS } ALL

I'd be happy to use non-portable syntax here...

As an aside, I'd vote to add the entire WINDOW syntax specification to the
Synopsis.  The main parameters section can remain as-is in order to aid in
reading comprehension - but having to search out the parameters area just
to remind oneself of the extremely verbose syntax is a bit annoying.  All
of the other sections are represented in both the main synopsis and the
parameters in this manner and I think WINDOW doesn't warrant an exception
(especially vis-a-via both the from_item and grouping_element
specifications).

David J.

[GENERAL] Make "(composite).function_name" syntax work without search_path changes?

[David G. Johnston]

CREATE SCHEMA altschema;
CREATE TYPE altschema.alttype AS ( altid text, altlabel text );

CREATE FUNCTION altschema.label(item altschema.alttype)
RETURNS text
LANGUAGE sql
AS $$
SELECT (item).altlabel;
$$;

WITH vals (v) AS (
SELECT ('1', 'One')::altschema.alttype
)
SELECT (v).label
FROM vals;

-- column "label" not found in data type altschema.alttype

SET search_path TO altschema;

WITH vals (v) AS (
SELECT ('1', 'One')::altschema.alttype
)
SELECT (v).label
FROM vals;

-- success

The system knows that the datatype being inspected is "altschema.alttype" -
would it be reasonable for the system to check for a function named "label"
in the same schema as the target type, "altschema", with the target
argument type and invoke it if present?

At this point I'm just writing: altschema.label(v) which is adequate but
not as clean.  I'm consciously trying to write queries that don't require
application schemas in the search path: including the joyous
operator(altschema.@@) syntax in some situations.  I suppose inference
could be considered in that situation as well.

David J.

Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?

[David G. Johnston]

On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras  wrote:

>
> 3. But they do log in with "developer" roles which are inherited from the
> owner role.
>
> ​[...]​

> I've tried it on a dummy database and it apparently works as described
> here. Is this by design?
>
>
​Not quite following but ownership is an inheritable permission; and even
if it was not SET ROLE is all that would be required.​  Any owner can drop
an object that it owns.


> What are the best practices for this sort of scenario where there is a
> single owner of all the schema (which is large), where developers need
> access to everything but cannot do something as drastic as dropping the dbs
> (and possibly tables)?
>

​Don't let developers into production databases...

Trusted people (and/or software) should be provided membership into
ownership groups.​  Developers should provide these people/programs with
vetted scripts to execute against production.  Developers can do whatever
they want on their local database instance with full schema-modifying
privileges.

"developers need access to everything" - there is a lot of nuance and
detail behind that fragment that is needed if one is going to develop a
data access and change management policy.

David J.

Re: [GENERAL] starting PG command line options vs postgresql.con

[David G. Johnston]

On Mon, Oct 30, 2017 at 6:48 AM, rakeshkumar464 
wrote:

> I would prefer using postgresql.conf.  what is the consensus in this forum
> regarding command line vs postgresql.conf.


​I suspect that most people administering a PostgreSQL database would
expect that the configuration file would be changed in lieu of passing
options via the command line.

Also if conflicting, which one
> takes priority.
>

​https://www.postgresql.org/docs/9.6/static/config-setting.html#AEN32498​

David J.

Re: [GENERAL] Old pg_clog files

[David G. Johnston]

On Sunday, October 29, 2017, Ron Johnson  wrote:

> Hi,
>
> v8.4.17
>
> http://www.postgresql-archive.org/pg-clog-questions-td2080911.html
>
> According to this old thread,  doing a VACUUM on every table in the
> postgres, template1 and TAPd databases should remove old pg_clog files.
>
>
> However, while about 40 of them have been deleted, 183 still exist.  What
> did I do wrong?
>
>

Reading the old thread it sounds like it might require multiple vacuums to
affect complete removal.

David J.

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[David G. Johnston]

On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis 
wrote:

> Got it, finally...
>
> insert into t_array select array[row((data_comp).*)::mytype[] from
> t_composite;
>
> I'm not sure why I need (data_comp).* rather than some of the other things
> that I tried and failed with...
>
>
​The ​unusual set of parentheses are so the parser interprets data_comp is
a column and not a table. Usually one write SELECT tbl.* FROM tbl so that
is the assumed meaning of "name".*

David J.

Re: [GENERAL] multiple sql results to shell

[David G. Johnston]

On Mon, Oct 23, 2017 at 7:08 AM, Mark Lybarger  wrote:

> I have this bash/sql script which outputs some curl commands.  the
> backticks causes it to get interpreted by the shell.   This works fine if
> there is one result, but when there are many rows returned, it looks like
> one shell command.
>
> any help on getting multiple rows returned to be executed by the shell
> would be appreciated!
>
> thanks!
>
> `psql -P "tuples_only=on" -h ${DB_HOST} -d ${DB_NAME} -U ${DB_USER} -c
> "select 'curl -X POST http://${REGISTER_HOST}:8080/' || source_id ||
> '/${MT}/' || model || '/' || site || '/backoffice/register' from
> myschema.events where source_id = $SOURCE_ID and ineffective_date is null"`
>
>
​You will need to, instead, "SELECT source_id, model, site​ FROM ..." to
return the raw record data to bash and then use bash's loop facilities to
dynamically generate and execute the curl command.

A second option, that I've never tried, is returning the full string but
not within a backtick command, then using bash looping simply invoke the
string like a normal command.

David J.

Re: [GENERAL] How to find out extension directory

[David G. Johnston]

On Fri, Oct 20, 2017 at 1:12 PM, rakeshkumar464 
wrote:

> I am documenting on automating installation of pgaudit extension for
> containers.  On my laptop I see that the directory where the files
> pgaudit.control and pgaudit--1.2.sql needs to be present is
>
> /usr/share/postgresql/10/extension.
>
> How do I know beforehand where the dir path is ?
>

I think pg_config (
https://www.postgresql.org/docs/current/static/app-pgconfig.html ) is what
you are looking for.

David J.

Re: [GENERAL] Is it OK to create a directory in PGDATA dir

[David G. Johnston]

On Thu, Oct 19, 2017 at 5:32 PM, John R Pierce  wrote:

> On 10/19/2017 1:25 PM, Tomas Vondra wrote:
>
> Is it fine to create a subdir inside PGDATA and store our stuff
> there, or will PG freak out seeing a foreign object.
>
>
> PostgreSQL certainly does not check if there are unknown directories in
> the data directory, and it will not crash and burn. But it causes all
> sorts of problems, and it increases the probability of human error.
>
>
> most importantly, ONLY the postgres system process should have access to
> the pgdata directory, it should have permissions 700.   your apps should be
> running as a different user, and that user won't have access to said PGDATA.
>
Untrusted languages in the server, and superuser use of COPY, count as
"applications" that are going to be running under the postgres user as far
as the O/S is concerned.

​I do agree that external applications should communicate with the server
via a session and not by mutual knowledge of a filesystem location.

I too would recommend not conflating system-related data that belongs in
PGDATA and application-related data that should reside outside of that
location.

​
​D
​avid J.​

Re: [GENERAL] Using Variables in Queries

[David G. Johnston]

On Thu, Oct 19, 2017 at 12:14 PM, Tom Lane  wrote:

> FROM  products,
>   (values ('red widget'::text)) consts(target)
> WHERE similarity(target, item_name) > 0.25
> ORDER BY target <<-> item_name
>
> PG 9.5 and up will flatten out cases like this to be exactly what you
> wrote out longhand.
>

​Does it matter if the values expression is embedded in a CTE?​  I find the
construct:

WITH constants AS (  VALUES (,,) )
SELECT * FROM tbl CROSS JOIN constants

nice as it keeps the constants at the top of the query string.  I presume
pure style differences between using multiple from items and an explicit
cross join.

David J.

Re: [GENERAL] Using Variables in Queries

[David G. Johnston]

On Thu, Oct 19, 2017 at 8:21 AM, Igal @ Lucee.org  wrote:

> Is it still true (the posts I see on this subject are quite old) that I
> can not do so in Postgres outside of a stored procedure/function?  And if
> so, what's the reason of not adding this feature?  Seems very useful to me.
>
​​

​PREPARE sqlquery AS​ SELECT * FROM products WHERE col1 LIKE $1 OR col2
LIKE $1;
EXECUTE sqlquery('red widget');

Alban's DO blocks are problematic since they are incapable of generating a
result set.

As Scott said people needing this functionality in PostgreSQL are content
with using psql.

Adding lots of new custom syntax to pure server-side parsed SQL is a
non-trivial undertaking whose need is reduced by the alternatives so
described (functions, DO block, PREPARE, psql).

David J.

Re: [GENERAL] Finally upgrading to 9.6!

[David G. Johnston]

On Wed, Oct 18, 2017 at 2:34 PM, Don Seiler  wrote:

> On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing 
> wrote:
>
>> On 10/18/2017 08:17 PM, Don Seiler wrote:
>>
>> > I disagree with this. It isn't my company's business to test the
>> > Postgres software in development, as much as it would be needed and
>> > appreciated by the community.
>>
>> Yeah, let others do it for you!  Great attitude.
>>
>
> It's a realistic, practical attitude. I'm sorry that not every company
> wants to offer the resources to contribute back to the community as much as
> you want. But it's foolish to expect a company to perform their development
> lifecycle against betas and RCs. They have their own products to worry
> about. A gallant few may let their DBAs do some sandbox testing to
> contribute time back to the community, but you can't expect them to.
>

​Both sides have made their point here - any more opinions or
justifications are going to just end up devolving into commentary that is
unacceptable on these lists.​  The community benefits from people who do
more than just run production servers while the business world has limited
resources to do not directly business related activities.  I feel that
those familiar with those dynamics are not surprised that someone would
choose to upgrade to 9.6.5 now since the 10.x series is still .0

If someone wants to espouse the business benefits of running pre-release
versions in staging environments against stable business code please start
a new thread focused on the "process" and not the "people".

David J.

Re: [GENERAL] Finally upgrading to 9.6!

[David G. Johnston]

On Wednesday, October 18, 2017, Joshua D. Drake 
wrote:
>
> I am not sure why this is even a question. There are plenty of businesses
> that can risk the deployment of a .0 release but there are also *MANY THAT
> CAN NOT*. The proper way to do this is to have a staging server running the
> .0 release that gets beaten on by the application for a few months and
> reports anything back to the community they find.
>

The continuum goes from having a staging server follow master/HEAD to
upgrading one version once a year as the earliest supported release gets
de-supported.  The closer to the first position you are contributing back
to the community and also the more quickly you can benefit from the new
features and enhancements each new release brings.

David J.

Re: [GENERAL] Finally upgrading to 9.6!

[David G. Johnston]

On Wed, Oct 18, 2017 at 8:16 AM, Igal @ Lucee.org  wrote:

> On 10/18/2017 7:45 AM, Ron Johnson wrote:
>
> On 10/18/2017 09:34 AM, Igal @ Lucee.org wrote:
>
> A bit off-topic here, but why upgrade to 9.6 when you can upgrade to
> 10.0?
>
>
> There's no way we're going to put an x.0.0 version into production.
>
>
> Then think of it as 9.7.0 but with an easier name to pronounce ;)
>

The OP likely intended to say "x.0" version; which a "[9.7].0" version is
just the same as a [10].0 version

The contributors do an excellent job but the reality of this community is
that a critical mass of people do not start seriously testing and using a
new version until it is officially released.  The first couple of bug-fix
releases are thus, unfortunately, likely to be non-trivial as the masses
flex the system at scales and using workloads that were not known or
available to the developers.  Its a balancing act for most and falling on
the side of waiting for a few point releases before promoting to production
is, I suspect, common.

David J.

Re: [GENERAL] Problems with the time in data type timestamp without time zone

[David G. Johnston]

On Wed, Oct 18, 2017 at 8:21 AM, américo bravo astroña <
americobr...@gmail.com> wrote:

> Hi,
>
> I have a program that saves information in a DB Postgresql need to extract
> data from date and time of that DB but when I retrieve the date and time
> information is always ahead 3 hours, the type of data that has that field
> is timestamp without time zone,
>
> Please forgive my english I'm using translator.
>

​A minimal SQL example of your problem would help.

David J.​

Re: [GENERAL] Simple query fail

[David G. Johnston]

On Tue, Oct 17, 2017 at 2:29 PM, Glenn Pierce  wrote:


> and I have a simple query that fails
>

​This is not failure, this is a query that found zero matching records.


>
> Ie
>
> SELECT sensor_id, MAX(ts), date_trunc('day', ts), COALESCE(MAX(value),
> 'NaN')::float FROM sensor_values_days WHERE ts > '2017-10-06
> 00:01:01+00' AND ts < '2017-10-06 23:59:59+00' GROUP BY 1, 3 ORDER BY
> 1, 2;
>  sensor_id | max | date_trunc | coalesce
> ---+-++--
> (0 rows)
>
>
> If I remove the timezone part of the start date I get results.
>
> Ie
>
>
>  ts > '2017-10-06 00:01:01'
>
> 597551 | 2017-10-06 01:00:00+01 | 2017-10-06 00:00:00+01 |13763
>
> I'm sure I am doing something silly but can't see what.

Does anyone know what is going on here ?
>

​The "max(ts)" result indicates a time of midnight, the 6th, GMT

ts > '2017-10-06 00:01:01+01' equates to > '2017-10-05 23:01:01+00'  of
which midnight, the 6th, GMT is indeed more recent

ts >  '2017-10-06 00:01:01+00' is 12:01:01 on the 6th, GMT, of which
midnight GMC, the 6th is NOT more recent

David J.

Re: [GENERAL] Using Substitution Variables In PostgreSQL

[David G. Johnston]

On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware 
wrote:

> Hi All,
>
> I wanted to find out how to use a substitution variable in an SQL
> statement that would cause the user to be prompted for a value. Something
> similar to the ampersand (&&) in ORACLE.
>
> For example, given the SQL statement below:
> SELECT ,,
> FROM 
> WHERE  = 35
>
> I want the user to be prompted for the value in the WHERE (filter) clause,
> e.g.
> SELECT ,,
> FROM 
> WHERE  = ?
>
> I would be glad if someone could point me in the right direction.
>

​That would be a client-side feature.  The only client supported on this
list is psql.  psql does not have this capability.  It does have the
"\prompt" ​meta-command which will serve in this specific case.

\prompt 'Enter a value for varname' varname

SELECT ... WHERE  = :'varname';

https://www.postgresql.org/docs/10/static/app-psql.html

David J.

Re: [GENERAL] REASSIGN OWNED simply doesn't work

[David G. Johnston]

On Fri, Oct 13, 2017 at 6:04 AM, Alvaro Herrera 
wrote:

> Sam Gendler wrote:
> > psql 9.6.3 on OS X.
> >
> > I'm dealing with a production database in which all db access has been
> made
> > by the same user - the db owner, which isn't actually a superuser because
> > the db runs on amazon RDS - amazon retains the superuser privilege for
> its
> > own users and makes non-superuser role with createrole and createdb
> > privileges for use as the primary role by the AWS account.
>
> It's true that REASSIGN OWNED is limited to a very particular scenario.
> It was written to support the specific case of wanting to drop a role,
> and that can only be done by a superuser, so why would it matter that
> REASSIGN OWNED itself could not be run by a superuser?
>

​You could at least fix the documentation bug since this superuser-only
restriction doesn't show up and is in fact contradicted by the sentence
​"REASSIGN OWNED requires privileges on both the source role(s) and the
target role."  The error message that comes back seems like it could be
improved as well.

The word "privileges" there seems odd too, wouldn't "membership" be more
appropriate?

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

David J.

Re: [GENERAL] Permissions for Web App

[David G. Johnston]

On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org  wrote:

> You mean that if I execute the ALTER DEFAULT command above as user
> `postgres` then only tables created by user `postgres` will give default
> privileges to role `webapp`?
>

​Yes.  "​You can change default privileges only for objects that will be
created by yourself or by roles that you are a member of."

You use the "FOR " modifier if you want the "or by roles that
you are a member of" portion to be used.

David J.

Re: [GENERAL] "Shared strings"-style table

[David G. Johnston]

On Fri, Oct 13, 2017 at 9:29 AM, Seamus Abshere <sea...@abshere.net> wrote:

> > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote
> > > Theoretically / blue sky, could there be a table or column type that
> > > transparently handles "shared strings" like this, reducing size on disk
> > > at the cost of lookup overhead for all queries?
> > > (I guess maybe it's like TOAST, but content-hashed and de-duped and not
> > > only for large objects?)
>
> On Fri, Oct 13, 2017, at 01:12 PM, David G. Johnston wrote:
> > Row-independence is baked into PostgreSQL pretty deeply...
>
> Could you say more about that?
>

​Not intelligibly...basically as far as PostgreSQL is concerned all the
data to reconstruct a row from a given table is present in that table.
>From a practical perspective the "TOAST table" for a table IS part of the
main table since it has no practical independent use.

As an aside I was thinking along the lines of an actual compression routine
which is what a spreadsheet file is able to do since a spreadsheet contains
the data from every row and column in a single file and is able to compress
the entire file by finding commonalities across rows and columns.  A
database generally cannot do that.

As for "transparent lookup tables for text columns"...I suppose one could
implement a "system-managed-enum" type with many of the same properties of
an actual enum but avoiding many of its problems by not exposing the
enum-ness to the user and instead just exposing the text labels...I suspect
faced with prospect of doing something that complex most users would just
setup a FK relationship.
​

> What about the comparison to TOAST, which stores values off-table?
>

TOAST solves a technical problem related to the fact that records "on the
table" have a very small size limitation (kb) while stored values can be at
least as large as a GB.  TOAST does involved compression but the input to
the compression algorithm is a single cell (row and column) in a table.​
As noted above I consider the TOAST table and main table to be a single
logical table.

Like I said the enum type has similar properties to what you want - but
Melvin is right that using it requires careful consideration of how your
data might change in the future.

David J.

Re: [GENERAL] "Shared strings"-style table

[David G. Johnston]

On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere  wrote:

> Theoretically / blue sky, could there be a table or column type that
> transparently handles "shared strings" like this, reducing size on disk
> at the cost of lookup overhead for all queries?
>
> (I guess maybe it's like TOAST, but content-hashed and de-duped and not
> only for large objects?)
>

Row-independence is baked into PostgreSQL pretty deeply...

I think an enum type is about as close are you are likely to get if you
don't wish to setup your own foreign-key relationships with surrogate keys.

David J.

Re: [GENERAL] [asking about how to upgrade docker postgresql without losing the data]

[David G. Johnston]

On Tue, Oct 10, 2017 at 4:25 AM, Olivani Prisila 
wrote:

> Hi,
>
> I am beginner both of docker and postgresql.
>
> How do i upgrade docker postgresql 9.5 into 9.6 without losing my
> current database?
> fyi: im using ubuntu verison 14 and docker 17.09
>

​More of a Docker forum question than PostgreSQL.  It depends on whether
you setup a distinct data container and/or mounted a host location into the
container to store the data.  In that case you should be able to link that
external dependency into the new runtime container in the same manner as it
is linked into the existing one.

If the data is directly within the runtime container it will be
considerably more difficult - though pg_dump/pg_restore might prove easiest
to accomplish.

David J.
​

Re: [GENERAL] Permissions for Web App

[David G. Johnston]

On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org  wrote:

> But I want to give that role permissions on future tables since I add new
> tables and drop/recreate current ones.
>

​ALTER DEFAULT PRIVILEGES​

​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

David J.
​

Re: [GENERAL] pg_start/stop_backup naming conventions

[David G. Johnston]

On Mon, Oct 9, 2017 at 9:33 AM, mj0nes  wrote:

> Hi,
>
> I'm just starting out on a rolling backup strategy and the naming
> convention
> has thrown me slightly for the WAL and "backup_label" files.
>
 ​[...]​

> Thanks for any pointers.
>

​I'll give out the standard advice - don't roll-your-own backup solution -
investigate and choose one the many supported and maintained backup
solutions (most are open source) that are already available.  This kind of
detail, then, should become unimportant to you.

David J.

Re: [GENERAL] Functions and Parentheses

[David G. Johnston]

On Fri, Oct 6, 2017 at 2:18 PM, Igal @ Lucee.org  wrote:

> Hi,
>
> Is current_date a function?  It's a bit puzzling to me since there are no
> parentheses after it, i.e.
>
>   SELECT current_date;
>
> And not
>
>   SELECT current_date();  -- syntax error
>
​
It, and the others like it, behave as functions.  They don't require
parentheses because the SQL standard​
​
​ defines them without parentheses.

> How come `current_date` has no parenthesis but `clock_timestamp()` does?
>
clock_timestamp isn't standard defined and unless the standard forces us to
do otherwise function invocation requires parentheses.

See 9.9.4 (
https://www.postgresql.org/docs/9.6/static/functions-datetime.html )

David J.

Re: [GENERAL] Setting search_path ignored

[David G. Johnston]

On Mon, Oct 2, 2017 at 7:09 AM, Guyren Howe  wrote:

> I logged out and back and did SET ROLE and got the same resullt.
>

​
Are you logging in as "thing_accessor" or some role that is a member of
"thing_accessor"?

David J.

​

Re: [GENERAL] Setting search_path ignored

[David G. Johnston]

On Mon, Oct 2, 2017 at 7:00 AM, Guyren Howe  wrote:

> CREATE ROLE thing_accessor;
>
> CREATE ROLE
>
> CREATE SCHEMA thing_accessor;
>
> CREATE SCHEMA
>
> covermything=> ALTER ROLE thing_accessor SET search_path=thing_accessor;
>
> ALTER ROLE
>
> covermything=# SET ROLE thing_accessor;
>
> SET
>
> covermything=> SHOW search_path;
>
>search_path
>
> -
>
>  "$user", public
>
> (1 row)
>
>
>
> This seems to contradict all the advice I can find about setting the
> schema search path.
>

​Settings associated with roles only take affect at session start/login.
 SET ROLE does not cause them to be read/executed.

https://www.postgresql.org/docs/9.6/static/config-setting.html

"Values set with ALTER DATABASE and ALTER ROLE are applied only when
starting a fresh database session."

David J.
​

Re: [GENERAL] Plan changes from index scan to seq scan after 5 executions

[David G. Johnston]

On Sat, Sep 30, 2017 at 10:57 AM, Alexander Kukushkin 
wrote:

> Hi,
>
> Recently I've been investigating a strange behavior of one stored
> procedure.
>

Please provide the output of:

SELECT version();

David J.
​

Re: [GENERAL] COPY vs \COPY FROM PROGRAM $$ quoting difference?

[David G. Johnston]

On Fri, Sep 29, 2017 at 9:27 AM, Alexander Stoddard <
alexander.stodd...@gmail.com> wrote:

> I found what seems to be an odd difference between COPY and \copy parsing.
>
​[...]
​


> COPY dest_table FROM PROGRAM $$ sed 's/x/y/' | etc... $$
>
> To my surprise this worked with COPY but not \COPY which failed with:
> \copy: parse error at "$$"
>
> Is this an undocumented difference? Is this even the appropriate email
> list to ask this kind of question or report such a difference?
>

​This is the correct place for seeking such clarification.​  The docs
cannot cover every possible thing people might do and these lists (-general
in particular) are here to fill in the gaps.

​The negative condition that "psql" itself doesn't understand
dollar-quoting​ is not documented.  Dollar-quoting is documented as a
server-interpreted SQL Syntax feature and only applies there.

While the commands are similar COPY is server-side SQL while \copy is a
psql meta-command that psql converts to SQL, executes, obtains the results,
and processes.  Note that the server would never see "PROGRAM $$" since the
server would be unable to access the local program being referred to.  The
server sees "FROM stdin" and psql feeds the results of the PROGRAM
invocation to the server over that pipe.

David J.

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

[David G. Johnston]

On Thu, Sep 28, 2017 at 1:08 AM, Denisa Cirstescu <
denisa.cirste...@tangoe.com> wrote:

> Hi Tom,
>
> You said that trapping an *arbitrary* exception is a “fairly expensive
> mechanism”.
>
​I suppose a better (though maybe not perfectly accurate) wording is that
setting up the pl/pgsql execution layer to trap "arbitrary SQL-layer
exceptions"​ is fairly expensive.  Even if the user specifies specific
errors the error handling mechanism in pl/pgsql is code for generic
(arbitrary) errors being given to it.

David J.

Re: [GENERAL] pg_upgrade?: Upgrade method from/to any version on random OS?

[David G. Johnston]

On Wed, Sep 27, 2017 at 12:48 PM, Hans Schou  wrote:

> I have looked through
> https://www.postgresql.org/docs/9.6/static/pgupgrade.html
> but it seems more complicated than necessary.
>

​[perform dump/restore]​

It went very good but took 100 minutes - where we had downtime - not so
> good.
>

​There is a correlation between the decreased downtime that pg_upgrade
facilitates and its complexity.​

If you cannot afford the 2 hours of downtime for dump/restore then likely
learning the pg_upgrade process would be the better choice for you.

David J.

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

[David G. Johnston]

On Mon, Sep 25, 2017 at 9:13 AM, Denisa Cirstescu <
denisa.cirste...@tangoe.com> wrote:

>
> Can someone please explain to me why this worked?
>
> What happened behind the scenes?
>
> I suspect that when you catch exceptions inside of a LOOP and the code
> ends up generating an exception, Postgres can’t use cached plans to
> optimize that code so it ends up planning the code at each iteration and
> this causes performance issues.
>
> Is my assumption correct?
>
>
>
​Not sure how much detail you are looking for but the docs say this:

"​Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than a block without one. Therefore, don't use
EXCEPTION without need."

https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

I'm somewhat doubting "plan caching" has anything to do with this; I
suspect its basically that there is high memory and runtime overhead to
deal with the possibilities of needing to convert a exception into a branch
instead of allowing it to be fatal.

David J.

Re: [GENERAL] hard parse?

[David G. Johnston]

On Thu, Sep 21, 2017 at 5:48 AM, Peter Koukoulis 
wrote:

> Hi
>
> I have a query where a filter would always be negative, how many steps,
> out these:
>
>- parsing and syntax check
>- semantic analysis
>- transformation process (query rewrite based on system or
>user-defined rules)
>- query optimization
>- execution
>
> would be performed or not? Also, where in the documentation can I found
> out which of the above phases would be performed?
>
> For example, for a query such as the following:
>
> select x,y from test1 where 1=0;
>
>
I'm inferring behavior here but...​

​All of them.  You are still going to get a result set with zero records
and the correct column structure.  i.e., "Execution".  None of the other
stuff can be skipped in getting to engine to that point.  With a "always
false" predicate and that simple of a query structure most of the other
stuff, including execution, is probably performed is seemingly zero time
but it still has to work through that step of the process - if nothing else
than to move through an if-branch to decide that nothing material needs to
be done.

David J.
​

Re: [GENERAL] Insert large number of records

[David G. Johnston]

On Tuesday, September 19, 2017, Job  wrote:

> and would not care about table partitioning (COPY command fire
> partitioned-table triggers).


You might want to write a script that inserts directly into the partitions
and bypass routing altogether.

Insert into ... select from ... is your only option for table-to-table and
you are stuck with whatever locks the execution of the command needs to
take.

David J.

Re: [GENERAL] reload postgresql with invalid pg_hba.conf

[David G. Johnston]

On Mon, Sep 18, 2017 at 12:36 PM, Tom Lane  wrote:

> jotpe  writes:
> > A system administration applied an invalid line into the pg_hba.conf
> > file and called "service postgresql reload". Since that command doesn't
> > return any error and leaves with exit code 0 it seams that new
> > configuration was applied.
>
> > Of course postgresql added a warning to the log file: "pg_hba.conf not
> > reloaded".
> > But even when I execute SELECT pg_reload_conf(), true is returned.
>
> > Is this the desired behavior?
>
> I wouldn't say it's desired behavior, exactly, but there's no very
> good way to improve it.  pg_ctl has no visibility into what the postmaster
> is thinking.
>

The function signature (and docs) for pg_reload_conf makes the OPs
interpretation understandable.  The docs and intuition would lead one to
assume that "true" means the reload was successful and "false" means it was
not (possibly with reasons emitted as notices/warnings/errors).​  But all
it tells us is whether a signal "was sent" and not whether it was
successfully acted upon by the other party.

Something like the attached makes this dynamic stand out.

https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

Cancel and terminate backends do have conditional return values so those
two seem OK.

pg_rotate_logfile() I suppose might want to be clarified here as well
though I suspect it is less prone to complications that pg_reload_conf is.

The description of pg_ctl doesn't lead to the same kind of assumptions
being made (i.e., it only speaks of sending a signal and has no declared
return value) though maybe an additional sentence wouldn't hurt...?

The docs on configuring the server to speak to this overall flow and what
happens when invalid values are encountered (i.e., they are ignored)

https://www.postgresql.org/docs/current/static/config-setting.html#CONFIG-INCLUDES

David J.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 641b3b8f4e..9ac7bf1e87 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18373,7 +18373,7 @@ SELECT set_config('log_statement_stats', 'off', false);
 pg_reload_conf()
 
boolean
-   Cause server processes to reload their configuration 
files
+   Cause server processes to attempt reloading their configuration 
files
   
   

@@ -18420,7 +18420,10 @@ SELECT set_config('log_statement_stats', 'off', false);

 pg_reload_conf sends a SIGHUP signal
 to the server, causing configuration files
-to be reloaded by all server processes.
+to be reloaded by all server processes.  The return value only indicates
+whether the signal was sent successfully to the postmaster.  It does not
+say whether the reload was performed and propogated successfully by
+the postmaster.

 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[David G. Johnston]

On Sun, Sep 17, 2017 at 1:13 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> I need to add a useful column, so that it would be easy to me to create a
> web script which would display today's and all past "daily puzzle" records
> - and wouldn't change the already published puzzles...
>
>
​Serial (i.e. integer/bigint with an sequence generator).  I would then add
another table that simply stores the "last manually reviewed id" as its
only record (or you could record manual reviews and take the max of that
field from the table).

David J.

Re: [GENERAL] looking for a globally unique row ID

[David G. Johnston]

On Thu, Sep 14, 2017 at 12:45 AM, Rafal Pietrak  wrote:

> Hello everybody,
>
> Can anybody help me find a way to implement an ID which:
>
> 1. guarantees being unique across multiple tables.
>
> 2. guarantees its uniqueness not only during INSERT, but also during the
> lifetime of the database/application (e.i. during future UPDATES).
>
> 3. guarantees persistence of value across database backup/restore/upgrade.
>
> an obvious candidate - a single SERIAL() (same serial) used in every
> table that needs that ID does not guarantee (2).
>

​I don't see how PostgreSQL can provide an absolute guarantee here.  As
others have been saying you can get very close, though.  Any of them have
the same basic property - you need to convert client SQL into "requests for
changes" and perform the actual changes within system-managed code while
restricting any possibility for applications to make those changes
themselves.  You can get a basic version of this using triggers.  Or you
can go all-out and write API functions for every one of these tables and
have the application perform CRUD only via these functions.  These
functions then, and not the application, would control key generation.​
 You disallow updating IDs and when inserting an ID you insert it into a
"id log" table that has a unique index on it and if that insertion succeeds
you can then associate it with the record being insert into the main
table.  You could setup an FK as well but it wouldn't provide the needed
guarantee.

Nothing will stop an administrator, or a user with incorrectly configured
permissions, from bypassing all of that but properly configured application
roles will be unable to do so.

David J.

Re: [GENERAL] Numeric numbers

[David G. Johnston]

On Saturday, September 2, 2017, Олег Самойлов  wrote:
>
> There is only 20 "3" after ".". Well, may be this is not a problem, but
> why are they infinite number of "0" after the point? I can write even
>
> => select (1::numeric/3-0.)*1e10;
> ?column?
> 
>  0.
>
> Result the same. According to the docs: "Numeric values are physically
> stored without any extra leading or trailing zeroes."
>
>
What you see on the screen is not what is physically stored.  The
documentation about the numeric type on the server is accurate, but
apparently the psql application takes some liberties as to what it chooses
to display.   It's display rules for numeric don't seem to be user
documented or configurable.

David J,

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

[David G. Johnston]

On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer  wrote:

> Hi.  I recently noticed that when doing a SELECT * with USING, that the
> join field(s) appear first in the output.  I'd never noticed that before,
> and was just curious if that is expected behavior or not.  Thanks.
>

​I don't recall if or where it is documented but it is intentional., as is
the documented fact that only one instance of the named column appears in
the output.

David J.

Re: [GENERAL] pgadmin - import a CSV with nulls?

[David G. Johnston]

On Thu, Aug 31, 2017 at 1:04 PM, George Neuner  wrote:

> Does anyone know a way to do this reliably?
>

​The psql "\copy" meta-command should be capable of doing what you desire.

David J.​

Re: [GENERAL] Table create time

[David G. Johnston]

On Wednesday, August 30, 2017,  wrote:

>
> Hi,
>
> is there a way to add a table create (and perhaps schema modify) timestamp
> to the system?
>
>
There is not.  You may wish to search the archives for discussions as to
why previous requests for this feature have not resulted in patches.

David J.

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[David G. Johnston]

On Mon, Aug 28, 2017 at 6:42 PM, Jerry Regan <
jerry.re...@concertoglobalresources.com> wrote:

> Let’s suppose for a moment that I piped the output of a psql instance to
> awk or some similar program, configured to detect the NOTIFY. That program
> would then spawn a process to actually perform the work, parameters being
> whatever is part of the NOTIFY. Both this psql instance and the awk script
> would be dedicated to this task.
>
> Given this is not intended in any way to be production quality code - in
> fact, it’s intended to deliver XML to the client server for validation
> (xmllint) in a development/test environment - do you see anything that
> clearly won’t work?  Also, this would be a very low volume connection.
> Perhaps one NOTIFY in five minutes - or longer.
>
>
​I've been curious about having a long-running psql instance that could be
controlled by an external process (named pipes/fifos I believe).  It seems
like you might actually have a chance to get that working if you, 1, intend
to perform the notification polling automatically​ and, 2, don't wish to
lose any notifications (i.e. you must keep the psql process that issues
LISTEN running continuously).  It seems you'd actually need two of these
since you don't actually want the output to be sent to stdout or a normal
file but rather a file that is linked to the stdin of yet another long
running process.

I believe *nix provides sufficient tools but whether psql is written to a
sufficient level of compatibility to leverage them is something I don't
know and, last time I mentioned this though, got one other person in the
same boat (thoughtful but not worth the effort to investigate and R) and
no one speaking up to claim they've done it already.

Given your rough description I'm not seeing why you wouldn't just have the
trigger write a record out to some kind of journal table and poll that
table for new records whenever you wish instead of depending upon
LISTEN/NOTIFY.

David J.

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[David G. Johnston]

On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
jerry.re...@concertoglobalresources.com> wrote:

> My concern is how, after LISTENing in psql, I can tell it what to do when
> the NOTItFY is received.
>

​As far as I am aware you cannot.  The docs for psql, and its feature set,
with respect to LISTEN, are minimal and basically say psql will print out
notifications to stdout (this I'm forced to assume or read the code) and
will poll for notifications whenever it sends a query to the server.

https://www.postgresql.org/docs/current/static/app-psql.html

​"​Whenever a command is executed, psql also polls for asynchronous
notification events generated by LISTEN and NOTIFY."

I suspect the feature request would be something like:

\set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent
meta-command)

And psql would invoke said program and pass the content of the notification
payload to it via stdin.

Given what we allow for "\copy" I don't see any technical or conceptual
problems with such a feature.  Just needs someone to be its primary author.

David J.

Re: [GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

[David G. Johnston]

On Wed, Aug 23, 2017 at 6:08 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johns...@gmail.com> writes:
> > I'm wondering if there is anything technical preventing someone from
> making:
>
> > DROP TEMP TABLE tablename;
>
> There is no great need for that because you can get the semantics you're
> asking for with "DROP TABLE pg_temp.tablename".
>

Furthermore, as a matter of good database management ideally the role
creating and dropping temporary tables is different from the role that
retains ownership​ of permanent tables - so that even if the wrong table
was selected object permissions would prevent its being dropped.

I don't think I'd -1 a patch that attempted to provide some incremental
improvement here but I just don't see one being written or getting enough
support to go through.  And that doesn't bother me - even if I was in a
position to do so I likely wouldn't write one at this point.

David J.

Re: [GENERAL] 'value too long' and before insert/update trigger

[David G. Johnston]

On Wednesday, August 23, 2017, Kevin Golding 
wrote:

> Presumably the length validation is being done before the trigger is run.
> Is there some way this could be changed so the trigger happens first?
>

The input tuple passed into the trigger is a valid record of the same type
as the table to which it is attached.  This means that while table
constraints are not enforced all column values must already be valid for
the defined column type.  I suspect changing this property of the system is
unlikely though I agree that I too have sometimes wished that a trigger
could be used to make invalid data, for a given type, valid.

Some hackish use of views, and triggers thereon, may provide an avenue to
gradual migration.

David J.

Re: [GENERAL] jdbc driver vis Release 10

[David G. Johnston]

On Wed, Aug 23, 2017 at 4:33 PM, Rob Sargent  wrote:

> I see no mention of a new jdbc driver on the release notes for Beta 1.
> Does that mean there isn't one?


​Whose release notes?  PostgreSQL Server?  I don't believe the server
release notes ever talk about external projects.​

Its seems quite a few significant changes (including version numbering)
have happened to the JDBC project in the past year but you will need to go
directly to the project's site/GitHub to keep abreast of all of it.

David J.

[GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

[David G. Johnston]

Hey all,

I'm wondering if there is anything technical preventing someone from making:

DROP TEMP TABLE tablename;

work.  Implementation wise the command would fail if a temporary table of
the given name doesn't exist.  Today, if a temporary table exists it will
be dropped, but if tablename is not schema-qualified and a table with the
same names exists in the search path it would be dropped.

Plus its symmetrical with CREATE [TEMP] TABLE.

David J.

Re: [GENERAL] Function not inserting rows

[David G. Johnston]

On Wed, Aug 23, 2017 at 8:23 AM, Frank Foerster  wrote:

>
> sql = "select * from api_dev.add_texts_to_item( %s, %s ); x x"
> i get the following python-error:
> psycopg2.ProgrammingError: FEHLER:  Syntaxfehler bei »s«
> LINE 1: ...dd_texts_to_item( 1234, ARRAY['PSYCOPG1', 'PSYCOPG2'] ); s s
>
> But the created statement looks syntax-wise identical to the
> pgadmin-statement (except for the forced error of course):
>
> select * from api_dev.add_texts_to_item( 444, array['PGADM1', 'PGADM2'] );
>
>
Try adding the following to your function to see what your function sees as
being the value of the p_item_texts argument.

RAISE ERROR '%', p_item_texts;

I'm assuming that:

self.cur_.execute( sql, (doc_id, isins, ) )

performs dynamic string substitution as opposed to generating a prepared
statement.  The placeholder values supplied to a prepared statement are
treated as literals.

You may also want turn on statement logging in the server.

David J.

Re: [GENERAL] What is the proper query

[David G. Johnston]

On Mon, Aug 21, 2017 at 9:08 PM, Igor Korot  wrote:

> Hi, ALL,
> draft=# SELECT * FROM information_schema.key_column_usage
>
​[...]​


> There are 3 foreign keys in that table.
>
> Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
>

Not using the key_column_usage view.  What that view is doing is basically
saying (my understanding from reading the docs, not testing it out):

CREATE TABLE tbl_pk
UNIQUE (col1, col2)​

CREATE TABLE tbl_fk
FOREIGN (col2, col1) REFERENCES tbl_pk (col1, col2)

Now your ordinal/position rows would be:

(1, 2)
(2, 1)

instead of:

(1, 1)
(2, 2)

if you had defined the FK and PK with the same column names in the same
order, like is done almost always and like you did in your example.

If you want to enumerate constraints you need to use a different
information_schema view or, as Melvin showed, use pg_catalog.  I'm not
fluent enough to provide examples.  If you provide the question/problem you
are trying to resolve others will likely offer suggestions.

David J.

Re: [GENERAL] What is the proper query

[David G. Johnston]

On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  wrote:

> Or this is the bug in 9.1?
> Since it looks like there are 2 columns with the same info in 1
> table/view
>

​This old email thread sounds similar to what you are describing here.

​https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com

David J.

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

[David G. Johnston]

On Fri, Aug 18, 2017 at 1:47 PM, Peter J. Holzer  wrote:

> So apparently
> ​ ​
> columnname open-parenthesis tablename closed-parenthesis is a specific
> syntactic construct, but I can't find it documented anywhere.


​The documentation linked to speaks mainly in terms of "composite types".
A table IS a composite type (i.e., there is an implicit one of the same
name) for these purposes.​

David J.

Re: [GENERAL] Where is pg_hba.conf

[David G. Johnston]

On Sunday, August 13, 2017, Igor Korot  wrote:

> Also, I presume that the address in this file is the address of the
> machine where the server is located, not the address from where the
> connection is initiated.
>

Not according to the docs.

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

David J.

Re: [GENERAL] sequence used on null value or get the max value for a column whith concurrency

[David G. Johnston]

Thinking aloud...

On Thu, Aug 10, 2017 at 3:05 PM, marcelo  wrote:

> In some table, I have a bigint column which at the app level can be null.
> Call it "DocumentNumber", and of course is not the PK.
> In most cases, the applications give some value to the column.
>
> But sometimes, the value remains null, expecting the backend or someone
> assign it a unique value.
>
> Could I use a sequence only when the field arrives to the backend as null?
> How? Using a triger?
>

Can you reserve a portion of the value range for auto-generated numbers
that application-assigned values will take on?  If so it would reasonably
simple to invoke nextval() in a trigger.

How could I get the max value for the column and increment it by one, but
> with concurrency warranty? Something as a table lock?
>

​One option would be to maintain the value in a separate table that you
update on insert using "UPDATE tbl SET col = col + 1 RETURNING col INTO
new_doc_num"

​You could probably make it an unlogged table as well and you'd return from
the trigger function with new_doc_num if its non-null otherwise you'd
branch and re-create the record before returning the just queried maximum +
1.

David J.

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[David G. Johnston]

On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson 
wrote:

> *>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well*
>
> *Possibly, but as the op is on 9.3, it is not available to him.*
>

​You should check the docs again...​


> *I would also argue that since* *"OWNER TO new_owner" is available in all
> other ALTER object statements, it is an omission and should be*
> *included for extenstions as well..*
>

​As am I, but omission or not I don't recall that we've ever back-patched
new SQL grammar.

David J.
​

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[David G. Johnston]

On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

>
>
> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>>
>>> Scott Marlowe <scott.marl...@gmail.com> writes:
>>> > Seems like something that should be handled by alter doesn't it?
>>>
>>> I have some vague memory that we intentionally didn't implement
>>> ALTER EXTENSION OWNER because we were unsure what it ought to do
>>> about ownership of objects belonging to the extension.  If the answer
>>> is "nothing" then it wouldn't be hard to add such a statement.
>>>
>>
>> The documented contract of CREATE EXTENSION(1)/ALTER EXTENSION ADD
>> MEMBER(2) requires that the extension owner and the owner of the member
>> objects be one-and-the-same (I suppose the inclusion of DROP in (2) makes
>> this debatable).  I do not know what happens today if someone tries to
>> ALTER OBJECT SET OWNER on a member object to a role other than the owner of
>> the extension.  From the docs I'd suggest that it should fail.  Likewise,
>> ALTER EXTENSION OWNER should cascade to all members - which (3), and normal
>> dependency tracking, seems to make straight-forward.
>>
>> 1>The user who runs CREATE EXTENSION becomes the owner of the extension
>> for purposes of later privilege checks, as well as the owner of any objects
>> created by the extension's script.
>>
>> 2>You must own the extension to use ALTER EXTENSION. The ADD/DROP forms
>> require ownership of the added/dropped object as well.
>>
>> 3>CREATE EXTENSION additionally records the identities of all the created
>> objects, so that they can be dropped again if DROP EXTENSION is issued.
>>
>> David J.
>>
>>
>
>
> *David,*
>
> *The problem is, The current owner of the extension needs to be dropped.
> No one should have to jump through hoops*
> *just to be able to do that. There is definitely a need for an*
>
> *ALTER EXTENSION name OWNER TO new_owner.*
> *As Tom Lane has already pointed out, it would not be hard to add that.*
>
>
​I'm not sure what it is you think I'm missing here.  My only point was I'm
tending to think that "nothing", while workable, diverges from what I would
expect - that an extension and all of its member objects should, at all
times, share a common owner.  I don't imagine that either definition would
be abnormally difficult to implement for v11.

I'm am wondering whether "REASSIGNED OWNED" needs fixing as well...since
that command is specifically designed to handle this use case.

https://www.postgresql.org/docs/9.6/static/sql-reassign-owned.html
​
​

D
​avid J.
​

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[David G. Johnston]

On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane  wrote:

> Scott Marlowe  writes:
> > Seems like something that should be handled by alter doesn't it?
>
> I have some vague memory that we intentionally didn't implement
> ALTER EXTENSION OWNER because we were unsure what it ought to do
> about ownership of objects belonging to the extension.  If the answer
> is "nothing" then it wouldn't be hard to add such a statement.
>

The documented contract of CREATE EXTENSION(1)/ALTER EXTENSION ADD
MEMBER(2) requires that the extension owner and the owner of the member
objects be one-and-the-same (I suppose the inclusion of DROP in (2) makes
this debatable).  I do not know what happens today if someone tries to
ALTER OBJECT SET OWNER on a member object to a role other than the owner of
the extension.  From the docs I'd suggest that it should fail.  Likewise,
ALTER EXTENSION OWNER should cascade to all members - which (3), and normal
dependency tracking, seems to make straight-forward.

1>The user who runs CREATE EXTENSION becomes the owner of the extension for
purposes of later privilege checks, as well as the owner of any objects
created by the extension's script.

2>You must own the extension to use ALTER EXTENSION. The ADD/DROP forms
require ownership of the added/dropped object as well.

3>CREATE EXTENSION additionally records the identities of all the created
objects, so that they can be dropped again if DROP EXTENSION is issued.

David J.

Re: [GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

[David G. Johnston]

On Tue, Aug 8, 2017 at 6:25 PM, Melvin Davidson 
wrote:

>
> *​H​ave you looked at the TUPLES ONLY option?*
>
> *-t* *--tuples-only*
>
> *Turn off printing of column names and result row count footers, etc. This
> is equivalent to the \t command.*
> *https://www.postgresql.org/docs/9.4/static/app-psql.html
> *
>
>
​Hadn't pondered it for this usage.  Now that I have I'd say having the
column names be visible for those few times the query returns results would
be nice.​  So, close but not quite.

I eventually saw, in one of the three descriptions for "tuples only" where
the caption is explicitly noted as being hidden in this mode. In my
proposed feature the caption, like the table it is attached to, would be
visible only conditionally.

David J.

[GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

[David G. Johnston]

Hey all, looking for thoughts on a feature request:

I run quite a few queries, using psql, that are intended for exceptional
situations.  When there are no results, which is expected, I still get the
table header and basic frame showing up in the output.  The option I'd like
is to be able to suppress the output of the empty table (and header if
there is one) or possibly substitute the empty table with user-supplied
text.

Thinking something that is used like \g

SELECT * FROM (VALUES (1)) vals (v) WHERE v = 0 \ghideifempty

[SQL] \galtifempty 'No values matching 0 in vals'

The names are descriptive, not suggestions...

David J.

[GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)

[David G. Johnston]

On Saturday, August 5, 2017, Dan Cooperstock at Software4Nonprofits <
i...@software4nonprofits.com
> wrote:
>
> As I have mentioned in several replies, I have tested all of this code
> directly in SQL statements and they work perfectly. It's only the
> interaction with PowerBuilder that isn't working right, which is why I keep
> saying that further answers from anyone that hasn't solved this problem of
> using Postgres with PowerBuilder, and getting identity retrieval to work in
> PowerBuilder, are not at this point worthwhile.
>

If it is PostgreSQL's problem you should be able to setup statement logging
on the server and see what is happening at the SQL level.

David J.

Re: [GENERAL] select md5 result set

[David G. Johnston]

On Wed, Aug 2, 2017 at 4:50 PM, Peter Koukoulis 
wrote:

> david, thanks for the help.
>
> Would this be the equivalent, for the statement in your email, for table
> TEST1 (x integer, y varchar(20)):
>
> ft_node=# SELECT md5(string_agg(vals::text, ''))
> ft_node-# from (select x,y from test1) vals(x,y);
> ?
>
>
​The subquery is redundant if you already have a table:

select md5(string_agg(test1::text, '')) from test1;

David J.​

Re: [GENERAL] select md5 result set

[David G. Johnston]

On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis 
wrote:

>
> SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
> from dual;
>
> MD5_VALUE
> 
> 
> 9FDA7FA725B783172CA371DA04AD5754
>
>
> Can I do something similar in PostgreSQL ?
>
>
​Similar.​

 SELECT md5(string_agg(vals::text, ''))
 FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)

​David J.

Re: [GENERAL] Would you add a --dry-run to pg_restore?

[David G. Johnston]

On Wed, Aug 2, 2017 at 10:10 AM, Edmundo Robles 
wrote:

> I  imagine   pg_restore can  execute  the instructions on dump but  don't
>  write on disk.   just like David said: "tell me what is going to happen
> but don't actually do it"
>

You may wish to respond to the actual points being made as to why
separating out "writing" from "execution" doesn't provide meaningful value
- especially not for the effort it would take.

David J.

Re: [GENERAL] Do not INSERT if UPDATE fails

[David G. Johnston]

On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber  wrote:

> However if the user record is not found or the user already has vip_until
> >= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
> would like to cancel the INSERT.
>
>
​You can "join" two DDL commands by using a Common Table Expression (CTE)
(i.e., WITH / SELECT)​.  You would need to make it so the UPDATE happens
first and if there are no results the INSERT simply becomes a no-op.

David J.

Re: [GENERAL] Would you add a --dry-run to pg_restore?

[David G. Johnston]

On Wed, Aug 2, 2017 at 9:02 AM, Edmundo Robles  wrote:

> Will be great to have a dry  run option, because   the time  to verify
>  reduces a lot and  will save space on disk, because just  execute  with no
> write to disk.
>

"Dry run", the way I understand it, can be accomplished via the "-l" (ell)
switch.  It means - "tell me what is going to happen but don't actually do
it".  I don't know what to call what you are describing but I don't see how
it could reasonably be made to work and give the admin confidence that a
true restoration would be valid.  Maybe I'm just being unimaginative but at
minimum you'd have to write out the tables to disk so data could be loaded
into them.  Then data would have to be persisted in order to validate the
constraints at the end.

If you are running out of disk space you should get larger/more disks.

Personally, I'd probably setup a dedicated "test restore" cluster with lots
of HD and put stuff like "fsync=off" into its postgresql.conf.

I could see having a "--make-tables-unlogged" option that would convert,
on-the-fly, all CREATE TABLE commands to "CREATE UNLOGGED TABLE" commands.

David J.

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

[David G. Johnston]

On Thursday, July 27, 2017, David G. Johnston <david.g.johns...@gmail.com>
wrote:

> On Thursday, July 27, 2017, Vincenzo Romano <vincenzo.rom...@notorand.it
> <javascript:_e(%7B%7D,'cvml','vincenzo.rom...@notorand.it');>> wrote:
>>
>> The main difference is that with RETURNS SETOF RECORD I still get the
>> "usual"(tm) function argument list in the usual place: between two
>> parentheses.
>> It's a matter of style. And a consistent one.
>> But I still don't get the point for not having it for a single column.
>>
>>
> Docs say: " When there are OUT or INOUT parameters, the RETURNS clause
> can be omitted. ". Sounds like you should you do just that.
>
>
Except you'd have no where to put the "setof" modifier...So, yeah, you
probably aren't going to personal style preference catered to here.

David J.

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

[David G. Johnston]

On Thursday, July 27, 2017, Vincenzo Romano 
wrote:
>
> The main difference is that with RETURNS SETOF RECORD I still get the
> "usual"(tm) function argument list in the usual place: between two
> parentheses.
> It's a matter of style. And a consistent one.
> But I still don't get the point for not having it for a single column.
>
>
Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can
be omitted. ". Sounds like you should you do just that.

David J.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[David G. Johnston]

On Mon, Jul 24, 2017 at 8:11 PM, Tom Lane  wrote:

> ​[*docs]
>  If the data were perfectly distributed, with the same
>  * number of tuples going into each available bucket, then the bucketsize
>  * fraction would be 1/nbuckets.  But this happy state of affairs will
> occur
>  * only if (a) there are at least nbuckets distinct data values, and (b)
>  * we have a not-too-skewed data distribution.  Otherwise the buckets will
>  * be nonuniformly occupied.


​Thanks, I have a better feel now.  Using this example (200 inner relation
rows) is pretty poor since at this scale there doesn't seem to be enough
data to make a noticeable difference.

But anyway, the above comment is only being applied when dealing with a
non-unique ​inner relation; however, the fraction used is 1/nbuckets for
any unique relation regardless of its size.

if (IsA(inner_path, UniquePath))
innerbucketsize = 1.0 / virtualbuckets;
else

And to clarify for others only reading this...the 200 on the "VALUES" node
is there because there are 200 literal values in the value_list.  The 200
on the resulting Hash (and HashAggregate in the example) node is there
because of DEFAULT_NUM_DISTINCT (changing the query limit to 300 only
changed the former).  Further, since it is only the default, the fraction
used charged out is 1/10 instead of 1/200 that would used if the 200 were a
real number instead - or 1/1024 if those 200 rows were known to be
themselves unique.

For me, I'm seeing that the expected number of input rows doesn't factor
into the innerbucketsize computation directly (possibly excepting a scaling
factor adjustment).

I can understand better, now, why this seemingly perfect example of a
semi-join query gets executed with an extra distinct/grouping node.

David J.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[David G. Johnston]

On Mon, Jul 24, 2017 at 7:58 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>>
>> The cost to form the inner hash is basically negligible whether it's
>> de-duped or not, but if it's not (known) de-duped then the cost
>> estimate for the semijoin is going to rise some, and that discourages
>> selecting it.
>>
>
> ​Why does the "hash semi join" care about duplication of values on the
> inner relation?  Doesn't it only care whether a given bucket exists
> irrespective of its contents?
>

​Rather, it cares about the contents is-so-far as confirming that at least
one of the tuples in the bucket indeed has the same joining value as the
outer relation (lost track of the fact that two values can share the same
hash).  But once it finds one it can move onto the new outer relation tuple
while an inner join would have to spend more time looking for additional
matches.

David J.
​

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[David G. Johnston]

On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane  wrote:

>
> The cost to form the inner hash is basically negligible whether it's
> de-duped or not, but if it's not (known) de-duped then the cost
> estimate for the semijoin is going to rise some, and that discourages
> selecting it.
>

​Why does the "hash semi join" care about duplication of values on the
inner relation?  Doesn't it only care whether a given bucket exists
irrespective of its contents?

Looking at those explains it would seem the "hash semi join" is simply an
inherently more expensive to execute compared to a "hash join" and that the
act of de-duping the inner relation would have to be quite expensive to
overcome the gap.  I cannot reconcile this with the previous paragraph
though...

Pointing me to the readme or code file (comments) that explains this in
more detail would be welcome.  Not sure what to grep for - "Hash Semi Join"
only turns up a couple of expected output results...

Thx.

David J.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[David G. Johnston]

On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin  wrote:

> ALTER TABLE ids ALTER COLUMN id SET NOT NULL;
> EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN
> :values_clause;
>
>  Aggregate  (cost=245006.46..245006.47 rows=1 width=8) (actual
> time=3824.095..3824.095 rows=1 loops=1)
>Buffers: shared hit=44248
>->  Hash Join  (cost=7.50..235006.42 rows=419 width=0) (actual
> time=1.108..3327.112 rows=3998646 loops=1)
>...
>

​You haven't constrained the outer relation (i.e., :values_clause) to be
non-null which is what I believe is required for the semi-join algorithm to
be considered.​

David J.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[David G. Johnston]

On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin  wrote:

> And I have one question. I don't understand why IN-VALUES doesn't use
> Semi-Join? PostgreSQL has Hash Semi-Join...  For which task the database
> has node of this type?
>

​Semi-Join is canonically written as:

SELECT *
FROM tbl
WHERE EXISTS (SELECT 1 FROM tbl2 WHERE tbl.id = tbl2.id)

The main difference between IN and EXISTS is NULL semantics.

David J.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[David G. Johnston]

On Sun, Jul 23, 2017 at 4:35 AM, dilaz03 .  wrote:

> - IN-VALUES clause adds new node to plan. Has additional node big
> overhead? How about filter by two or more IN-VALUES clause?
>

​IN-VALUES is just another word for "TABLE" which is another word for
"RELATION".  Writing relational database queries that use explicit
relations is generally going to give you the best performance.

Basically you want to write something like:

SELECT *
FROM ids
JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id)​

or

WITH vc AS (SELECT vid FROM  ORDER BY ... LIMIT )
SELECT *
FROM ids
JOIN vc ON (vid = ids.id)

"IN ('l1','l2','l3')" is nice and all but as demonstrated the mechanics of
executing that are different, and slower, than processing relations and
tuples.  For a small number of items the difference is generally not
meaningful and so the convenience of writing (IN (...)) is worth taking.

David J.

Re: [GENERAL] What is the problem with this query?

[David G. Johnston]

On Sunday, July 23, 2017, Igor Korot <ikoro...@gmail.com> wrote:

> Hi, David,
>
> On Sun, Jul 23, 2017 at 5:07 PM, David G. Johnston
> <david.g.johns...@gmail.com <javascript:;>> wrote:
> > On Sunday, July 23, 2017, Igor Korot <ikoro...@gmail.com <javascript:;>>
> wrote:
> >>
> >> Is "IF" operator not supported by PostgreSQL
>
> So how do I write this properly?
>
>
CREATE FUNCTION or a DO block. See docs for usage.

Since the language is pl/pgsql you can learn how to write it here.

 https://www.postgresql.org/docs/9.6/static/plpgsql.html

David J.

Re: [GENERAL] What is the problem with this query?

[David G. Johnston]

On Sunday, July 23, 2017, Igor Korot  wrote:
>
> Is "IF" operator not supported by PostgreSQL
>

IF is pl/pgsql, not SQL.

David J.

Re: [GENERAL] Postgres csv logging

[David G. Johnston]

It is customary to indicate when you've posted the same question to other
forums - in this case SO.

https://dba.stackexchange.com/questions/180263/postgres-log-query-and-command-tag-to-csv

As the comment there says your config and your output seem at odds.  Though
I think your confusion is still adequately represented.

On Wed, Jul 19, 2017 at 4:41 PM, Alessandro_feliz <
alessandro_fe...@hotmail.com> wrote:

> Why is the first line, that has the query executed, saying that the command
> tag is "idle", shouldn't it say "SELECT"?


"Command tag: type of session's current command" - given your example I
suppose this means "initial state of the session" as opposed to being a
category tag of what the current line is doing.  What you are seeing is
"start at idle and parse" the incoming statement text.  That is one action
- parse.  The subsequent action is "execute" the contents of the SELECT
that is now in the active state of the session.

If the above is correct the docs could be more clear since I'd agree that
your interpretation of the field seems more logical - though knowing
initial state is quite useful in its own right.

David J.

Re: [GENERAL] Backward compatibility

[David G. Johnston]

On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot  wrote:

> MySQL uses this:
> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
> Is it safe to assume that PostgreSQL calculates the version the same way?
>
​
Yes and no.  Things are changing with this next release.  The next two
major releases will be:

10.x  (or 10.0.x using historical nomenclature - 1000xx)
11.x (or 11.0.x using historical nomenclature - 1100xx)

For prior releases the major versions are:

9.2.x
9.3.x
9.4.x
9.5.x
9.6.x

If you want to consider the 9 to be "major" and the .[2-6] to be minor for
mechanical purposes that's fine but the change from 9.5 to 9.6 is a major
change with backward incompatibilities - which a minor change doesn't
allow.  In the new setup the thing you call "minor" will always remain at
zero in order to eventually mitigate the need to have this kind of
discussion. Since it is always going to be "0" we simply omit printing it.

David J.

Re: [GENERAL] Backward compatibility

[David G. Johnston]

On Thursday, July 20, 2017, David G. Johnston <david.g.johns...@gmail.com>
wrote:

> On Thursday, July 20, 2017, Igor Korot <ikoro...@gmail.com
> <javascript:_e(%7B%7D,'cvml','ikoro...@gmail.com');>> wrote:
>
>> Hi, David,
>>
>> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
>> <david.g.johns...@gmail.com> wrote:
>> > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> >> Is there a query or a libpg function which can return the version of
>> >> the server I'm running?
>> >
>
>
> Its PQ not PG - and I'd doubt it but I don't directly use libpq.
>
>
Actually, The docs do cover how to do this directly in libpq.

David J.

Re: [GENERAL] Backward compatibility

[David G. Johnston]

On Thursday, July 20, 2017, Igor Korot <ikoro...@gmail.com> wrote:

> Hi, David,
>
> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
> <david.g.johns...@gmail.com <javascript:;>> wrote:
> > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikoro...@gmail.com
> <javascript:;>> wrote:
>
> >> Is there a query or a libpg function which can return the version of
> >> the server I'm running?
> >


Its PQ not PG - and I'd doubt it but I don't directly use libpq.


> Is there a way to get a version_major, version_minr and version_extra?
>
>
There is no 'extra'.  The last two digits are the minor and everything
before is the major.  Usually you'd use inequality comparisons so there'd
be no point.  If here is a way beside parsing I'm not aware of it.

David J.

Re: [GENERAL] Backward compatibility

[David G. Johnston]

On Thu, Jul 20, 2017 at 7:23 PM, Igor Korot  wrote:

> On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer
>  wrote:
> >
> >>Is there a query or a libpg function which can return the version of
> >>the server I'm running?
>


> > Select version();
>
> Here is the results:
>
> draft=# SELECT version();
>
>
>   version
> 
> 
> -
>  PostgreSQL 9.1.24 on x86_64-apple-darwin, compiled by
> i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
> build 5658) (LLVM build 2335.6), 64-bit
> (1 row)
>
> Is there a way to get just "9.1.24" without everything else?
>

​SHOW server_version_num;

90124​ should be the result (don't have that version installed to
copy-paste)

90506 is the version I have at my fingertips.

David J.

Re: [GENERAL] Backward compatibility

[David G. Johnston]

On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot  wrote:

> Hi, ALL,
> According to the documentation PostgreSQL 9.6 (latest) supports
>
> CREATE INDEX IF NOT EXIST
>
> However, the version 9.4 and below supports only
>
> CREATE INDEX.
>
> Is there a query or a libpg function which can return the version of
> the server I'm running?
>

​SHOW server_version_num;  -- this is better than "version()" since you
don't have to parse text

​
https://www.postgresql.org/docs/9.6/static/runtime-config-preset.html#GUC-SERVER-VERSION-NUM

see the below for other ways to query the setting.

https://www.postgresql.org/docs/9.6/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION


> And in the latter case - is there a way to check if the index exist?
>
> I guess I will have to query "information_schema" again...
>
>
​Yep, not much demand for multiple ways to do the same thing in this
area...​

​David J.​

Re: [GENERAL] ~/.psqlrc file is ignored [solved: $HOME/.psqlrc]

[David G. Johnston]

On Thu, Jul 20, 2017 at 8:37 AM, vstuart  wrote:

> ​​
> [victoria@victoria ~]$ pg
>   [sudo -u postgres -i]
>   [sudo] password for victoria:
>

​Just curious, what is going on here?

David J.
​

Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there

[David G. Johnston]

On Wed, Jul 19, 2017 at 8:53 PM, Guyren Howe  wrote:

> Thanks. Seeking greater understanding, why is json_agg(*) not equivalent?
>

​Are you referring to the fact that ​this provokes an error?

"select json_agg(*) from schemata;"

The json_agg(expression) function has an arity of 1 (i.e., one input
argument only).  If you write:

select schemata from schemata;

you get one column in the output, while:

select * from schemata

results in an output relation having three columns - the "*" expands the
composite type in the FROM clause into its component columns in the
select-list

json_agg(*) fails since it is not expecting 3 columns (though oddly the
error I see in 9.6 indicates its actually looking for a 0-arity function...
"function json_agg() does not exist" - which implies that generally one
cannot place a * in a function call, even if it would result in a single
column.  This may even be documented but I haven't the desire to look right
now...)

David J.

Re: [GENERAL] ~/.psqlrc file is ignored

[David G. Johnston]

On Wed, Jul 19, 2017 at 12:12 PM, vstuart  wrote:

> Hi David: I see what you are saying; sorry for the confusion. This is how
> postgres operates on my system:
>

​None of that is surprising or unique.  If you ask specific questions I'd
be willing to answer them but I'm not going to explain everything that is
going on line-by-line.

David J.
​

Re: [GENERAL] ~/.psqlrc file is ignored

[David G. Johnston]

On Wed, Jul 19, 2017 at 11:49 AM, vstuart  wrote:

> As a simple solution, I can sudo symlink MY ~/.psqlrc to that directory
> (/var/...; changing ownership also to postgres), but there appears to be
> some underlying issue, as Pg should find ~/.psqlrc, correct?
>

​What does "your" ~/.psqlrc have to do with any of this?  If you are
executing psql while masquarding at the postgres user its the postgres
user's​ ~/.psqlrc file that will be looked for.

Typically one doesn't execute psql as the postgres user.  They configure
the system so that they can execute psql as a regular user.  You should
probably do that and then everything should work like you are thinking.

David J.

Re: [GENERAL] PG 9.1 - FK + Check constraint

[David G. Johnston]

On Tue, Jul 18, 2017 at 6:56 PM, Patrick B  wrote:

> Another solution would be to create an IMMUTABLE functiondoing the check
> and use that in a CHECK constraint [1].
>
Why do you need an FK constraint?  Why can you not use a data modification
trigger?

Placing the subquery within a mis-defined immutable function so PostgreSQL
is oblivious to it doesn't do anything to overcome the fact that the system
is not designed to have check constraints with subqueries.  You are likely
to get burned - commonly during backup restoration but there is no
guarantees as to why might happen.

David J.

Re: [GENERAL] What is exactly a schema?

[David G. Johnston]

On Fri, Jul 14, 2017 at 9:13 AM, marcelo  wrote:

> I'll be using Devart's dotConnect. I have two alternatives at this moment
>
> a) To set the user name to the required schema. This has the (little)
> drawback that forces user configuration for every schema...
>

​With "ALTER ROLE SET" the user name and initial search_path are both
explicitly specified and do not need to match...​

b) To manually do something like the JDBC driver you mention, but it
> triggers some questions
> b.1) To execute the set search_path one must be connected, database name
> included. I think by that time, the default schema is determined. Or I am
> wrong, am I?
>

​You are mistaken.  When doing it at the connection-level an actual "SET
search_path" SQL command is not generated - instead the server simply uses
the data present in the connection string to change runtime variables
before the connection is made ready for use.


> b.2) The search_path is valid for the database or restricted to the
> connection?
>

​You need to rephrase this question.  From a client's perspective
everything is restricted to the connection - and a given connection only
exists to a single database at any given time.

And if you can avoid top-posting it would be much appreciated.

David J.
​

Re: [GENERAL] What is exactly a schema?

[David G. Johnston]

On Fri, Jul 14, 2017 at 9:01 AM, Jerry Sievers 
wrote:

> marcelo  writes:
>
> > Thank you.
> > Now I have a related question.
> > Could I select a specific schema in the connection string? Say, by
> > example database=mydb.schemanumbertwo ?
> >
> > I'm asking this because I will be using Devart's dotConnect and Entity
> > developer to access the database. I have not full control, so I cannot
> > set the search path immediately after the connection.
>
> Take a look at the PGOPTIONS env variable an/or libpq connect parameter
> variant.
>

Yes, it can be done, but doing so precludes the use connection pooling
technology (at least pg_bouncer, anyway, I suspect others).

David J.

Re: [GENERAL] Event Trigger question

[David G. Johnston]

On Thursday, July 13, 2017, ProPAAS DBA  wrote:

>
> 2) where can I find a complete list of the tg_ variables? I see this 
> list:https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html
>
> which includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are not in the 
> list. Are there other variables I can reference?
>
>
They are listed at the bottom of the page you linked to.

David J.

Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

[David G. Johnston]

On Tue, Jul 11, 2017 at 8:36 AM, Seamus Abshere  wrote:

> Given an update that uses CTEs like this:
>
> WITH
> lock_rows AS (
>   SELECT 1 FROM tbl WHERE [...] FOR UPDATE
> )
> UPDATE [...]
>
> Will the rows in `tbl` remain locked until the UPDATE is finished?
>
>
​Yes​ - locks persist to the end of the transaction.  Using a CTE doesn't
constitute creating a new statement.

Also, does it matter if `lock_rows` is referenced? (IIUC the query
> wouldn't be run if the CTE isn't referenced if it was for a SELECT, but
> since it's an UPDATE, it will be run anyway
> ​.
>

​Pretty sure it will not be.  The EXPLAIN​ command should be able to
provide a more definitive answer.

If the UPDATE was inside the CTE it definitely would be run regardless of
outer query references.  I'm not sure if the FOR UPDATE impacts whether the
select needs to be executed by I'm thinking no since it doesn't change the
semantics of the query.

David J.

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

[David G. Johnston]

On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> However there is a problem: I can not use a "single-instance" cronjob to
> run words_expire_games hourly.
>

​Setup a cron job that invokes the servlet - probably via "curl"

My question is if I should ensure that only 1 servlet runs the custom
> PL/pgSQL function by using "synchronized" in Java as I do it right now:
>

​Probably not.  UPDATE takes out a lock that will prevent other updates
from acting on the same records concurrently.​


> Or if maybe there is some condition (maybe "UPDATE SKIP LOCKED"?) I could
> add to my custom function copy-pasted below? -
>

Why are you trying random syntax that isn't documented?

https://www.postgresql.org/docs/9.6/static/sql-update.html​

UPDATE  words_games
> SET finished = CURRENT_TIMESTAMP
> WHERE   finished IS NULL
>

​That should be sufficient.  Do you have any examples that show it is not?

In short, one of the main reasons for "UPDATE RETURNING" is so that one
needn't determine the records to be updated separately from the actual act
of updating.  Instead you update first and then capture the results for
subsequent use.

David J.

Re: [GENERAL] psql doesn't pass on exported shell environment functions

[David G. Johnston]

On Fri, Jul 7, 2017 at 8:45 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johns...@gmail.com> writes:
> > ​I'm using Ubuntu 16.04
>
> Hmph.  Works for me on RHEL6.  I'm betting that Ubuntu has put in some
> weird security restriction, possibly an overreaction to the "shellshock"
> bug which was in the exported-functions feature.
>

​Thank you both for looking at this.  While my curiosity is still piqued
I've solved the original problem without resorting to exporting a function
through a psql invocation.  Fortunately, exported functions are still seen
by directly called bash scripts​ so a few other areas where I leverage that
feature are still working.

I've haven't totally discounted local configuration interplay here - my
bashrc is non-trivial and there a few differences between the setups
besides versions - though a security enhancement in 16.04 seems plausible.

David J.

Re: [GENERAL] psql doesn't pass on exported shell environment functions

[David G. Johnston]

On Fri, Jul 7, 2017 at 8:19 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Jul 7, 2017 at 8:12 AM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> ​I'm using Ubuntu 16.04
>>
>>
> ​Seems to be a regression since this works on my 14.04 setup.
>
>
​Sorry for the append but my 14.04 setup is using 9.5 while the 16.04 setup
is using 9.6.

David J​.

Re: [GENERAL] psql doesn't pass on exported shell environment functions

[David G. Johnston]

On Fri, Jul 7, 2017 at 8:12 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> ​I'm using Ubuntu 16.04
>
>
​Seems to be a regression since this works on my 14.04 setup.

David J.
​

Re: [GENERAL] psql doesn't pass on exported shell environment functions

[David G. Johnston]

On Fri, Jul 7, 2017 at 7:43 AM, Albe Laurenz <laurenz.a...@wien.gv.at>
wrote:

> David G. Johnston wrote:
> >> It works for me on Linux with 9.6.3 psql:
> >
> > ​Except you haven't recreated the scenario I presented.​
> >
> > ​You only are involving a single script and that script defines
> "testfunction" itself
> > (which makes exporting pointless).  In my example the script being
> executed within the
> > psql script does not define testfunction itself.
> >
> > -> == execute in subshell
> >
> > ​main-script (def func) -> psql -> psql-call-bash (invoke func)
>
> I am confused; my shell script does *not* contain a function definition.
>
> Am I missing something?
>

​Nope, it was a combination of ERRNOCAFFINE and not seeing a second script.

​I'm using Ubuntu 16.04

I tried your way of not using a "main-script" and got the same result.

I can see the exported environment variable TEST_ENVVAR in the "env" output
of the psql \! subshell.  "declare -Fx" shows no results.

I replaced "/usr/bin/env bash" with "/bin/bash" to no effect.

David J.