Re: [GENERAL] PG 10 and perl

[Andy Colson]

On 09/29/2017 09:32 AM, Tom Lane wrote:

Andy Colson  writes:

I started playing with 10, and cannot get it to use the right version of perl.  
I'll only use the system version:
root@firefly:/usr/local/pg10/lib/postgresql# ldd plperl.so
libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x7fc9c67d4000)


Hm, I don't think we've changed anything about that in v10.


But, here's the thing.  The config.log reports 5.26 version:


That's good, but what's probably missing is an "rpath" specification when
linking plperl.so.  Did you build with --disable-rpath, perhaps?  That's
the usual recommendation when building for Linux, but if you want to use
any non-default libraries, you can't do it.

Go into src/pl/plperl, remove and remake plperl.so, and see whether the
link command includes anything like

-Wl,-rpath,'/usr/lib64/perl5/CORE',--enable-new-dtags

(That's what I see when building with a stock Linux Perl configuration and
rpath enabled.)  If there's no such switch, or if it doesn't point to
where the libperl.so that you want to use is, then there's your problem.

regards, tom lane



tldr: PG 10 compiles fine and works with perlbrew fine, I confused install 
paths.

I removed the plperl.so, and typed make:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement 
-Wendif-labels -Wmissing-format-attribute -Wformat-security 
-fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC 
-DLINUX_OOM_SCORE_ADJ=0 -fPIC -shared -o plperl.so plperl.o SPI.o Util.o  
-L../../../src/port -L../../../src/common -Wl,--as-needed 
-Wl,-rpath,'/opt/perl5/perls/perl-5.26/lib/5.26.1/x86_64-linux-thread-multi/CORE',--enable-new-dtags
  -fstack-protector-strong -L/usr/local/lib  
-L/opt/perl5/perls/perl-5.26/lib/5.26.1/x86_64-linux-thread-multi/CORE -lperl 
-lpthread -lnsl -ldl -lm -lcrypt -lutil -lc


And in the src dir:
root@firefly:/tmp/SBo/postgresql-10rc1# ldd ./src/pl/plperl/plperl.so
libperl.so => 
/opt/perl5/perls/perl-5.26/lib/5.26.1/x86_64-linux-thread-multi/CORE/libperl.so 
(0x7f33b9f87000)


I had 10beta1 installed to /usr/local/pg10.
The SlackBuild script I'm using installs to /usr/local/pg95 still.  So I was 
compiling and installing 10rc1 into pg95.

Sorry for the noise (and thanks Tom).

-Andy


--
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] Converting inherited partitions into declarative ones

[Victor Yegorov]

2017-09-29 20:32 GMT+03:00 Victor Yegorov :
>
> Is it possible to avoid Full Scan here? I have TBs worth of data in
> partitions,
> so it'll takes ages to switch to the declarative partitioning the way
> things stand now.
>

OK, looking at the source code helped — I need to change `created_at`
column to be `NOT NULL`.
After this change things are working as expected.

I wonder if it's possible to adjust documentation here:

This does not work, however, if any of the partition keys is an expression
and the partition does not accept NULL values
or partitioning column is missing NOT NULL constraint.

?


-- 
Victor Yegorov

Re: [GENERAL] Converting inherited partitions into declarative ones

[Melvin Davidson]

On Fri, Sep 29, 2017 at 1:32 PM, Victor Yegorov  wrote:

> Greetings.
>
> I am looking into new partitioning of 10rc1 on a copy of a production
> system.
> And I'm having tough times with the full scan.
>
> Per documentation:
> > It is possible to avoid this scan by adding a valid CHECK constraint to
> the table
> > that would allow only the rows satisfying the desired partition
> constraint before
> > running this command. It will be determined using such a constraint that
> the table
> > need not be scanned to validate the partition constraint.
>
>
> So I have this table with CHECK constraint:
>
> test=# \d stats_201503
>  Table "public.stats_201503"
>Column   Type Collation
> Nullable Default
>  --- -
>  
> …
> created_at   timestamp without time zone
> …
> Check constraints:
> "stats_201503_created_at_check" CHECK (created_at >= '2015-02-28
> 19:00:00'::timestamp without time zone AND created_at < '2015-03-31
> 20:00:00'::timestamp without time zone)
>
>
>
> Still, if I try to attach it, I get Full Scan:
>
> test=# ALTER TABLE jsm ATTACH PARTITION stats_201503 FOR VALUES FROM
> ('2015-02-28 19:00:00') TO ('2015-03-31 20:00:00');
> ALTER TABLE
> Time: 55502.875 ms (00:55.503)
>
>
>
> Is it possible to avoid Full Scan here? I have TBs worth of data in
> partitions,
> so it'll takes ages to switch to the declarative partitioning the way
> things stand now.
>
> Thanks in advance.
>
>
> --
> Victor Yegorov
>


*>Is it possible to avoid Full Scan here? *

*Have you verified that constraint_exclusion is set to "on" or "partition"
?*

*https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html
*
* *
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Converting inherited partitions into declarative ones

[Victor Yegorov]

Greetings.

I am looking into new partitioning of 10rc1 on a copy of a production
system.
And I'm having tough times with the full scan.

Per documentation:
> It is possible to avoid this scan by adding a valid CHECK constraint to
the table
> that would allow only the rows satisfying the desired partition
constraint before
> running this command. It will be determined using such a constraint that
the table
> need not be scanned to validate the partition constraint.


So I have this table with CHECK constraint:

test=# \d stats_201503
 Table "public.stats_201503"
   Column   Type Collation
Nullable Default
 --- - 

…
created_at   timestamp without time zone
…
Check constraints:
"stats_201503_created_at_check" CHECK (created_at >= '2015-02-28
19:00:00'::timestamp without time zone AND created_at < '2015-03-31
20:00:00'::timestamp without time zone)



Still, if I try to attach it, I get Full Scan:

test=# ALTER TABLE jsm ATTACH PARTITION stats_201503 FOR VALUES FROM
('2015-02-28 19:00:00') TO ('2015-03-31 20:00:00');
ALTER TABLE
Time: 55502.875 ms (00:55.503)



Is it possible to avoid Full Scan here? I have TBs worth of data in
partitions,
so it'll takes ages to switch to the declarative partitioning the way
things stand now.

Thanks in advance.


-- 
Victor Yegorov

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

[Alexander Stoddard]

On Fri, Sep 29, 2017 at 11:54 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> 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.
>
>
Thank you, David. That helps makes sense of everything. There is the
situation where psql is executed by a non-superuser on the server. But the
docs make clear that only STDOUT / STDIN, not not named files or commands
are allowed in that case. So I now realize I would just have been trading a
parse error for a security one had my dollar-quoting worked with \copy!

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.

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

[Alexander Stoddard]

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

I am using a bash pipeline of sed commands to clean up a source data file
before importing it into a table.  This works fine when working from the
command line and piping the result to psql on STDIN.

However I attempted to put this same workflow into a psql script (as
opposed to a shell script). To avoid quoting issues with the shell pipeline
I put my pipeline command string in dollar quotes.

eg.
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?

Thank you,
Alex Stoddard

Re: [GENERAL] PG 10 and perl

[Andy Colson]

On 9/29/2017 9:28 AM, kbran...@pwhome.com wrote:

Andy Colson wrote:
In PG 9.5 this worked fine, and compiling and running PG used the 5.26 version 
of perl:
root@firefly:/usr/local/pg95/lib/postgresql# ldd plperl.so
libperl.so => 
/opt/perl5/perls/perl-5.26/lib/5.26.1/x86_64-linux-thread-multi/CORE/libperl.so 
(0x7f81e8bde000)

I started playing with 10, and cannot get it to use the right version of perl.  
I'll only use the system version:
root@firefly:/usr/local/pg10/lib/postgresql# ldd plperl.so
libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x7fc9c67d4000)

But, here's the thing.  The config.log reports 5.26 version:

configure:7752: checking for perl
configure:7770: found /opt/perl5/perls/perl-5.26/bin/perl
configure:7782: result: /opt/perl5/perls/perl-5.26/bin/perl
configure:7804: using perl 5.26.1
...



I'm about to go down this path to prepare for 10.0, but I haven't had time yet.

You don't show your configure command, but did you set PERL there as well as 
have it in your PATH? Here's what I've done in the past:

 ./configure --prefix=/opt/pg-9.5 --with-system-tzdata --with-perl 
PERL=/opt/perl/bin/perl

and it used our custom Perl just fine. If you did that, I'm not sure what else 
to say at the moment. I hope to get around to checking out RC1 early next week 
though and I'll be using 5.26 as well.

Hmm, given your report, I'll try to move it up my ToDo list to give myself more 
time. I'll report back here if I get it to work and haven't seen a reply by you 
about you getting it to work also.

Kevin




I only use --with-perl, that's all I've ever used.  I didnt know you 
could pass PERL=/opt/perl/bin/perl.


-Andy


--
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] PG 10 and perl

[Tom Lane]

Andy Colson  writes:
> I started playing with 10, and cannot get it to use the right version of 
> perl.  I'll only use the system version:
> root@firefly:/usr/local/pg10/lib/postgresql# ldd plperl.so
>   libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x7fc9c67d4000)

Hm, I don't think we've changed anything about that in v10.

> But, here's the thing.  The config.log reports 5.26 version:

That's good, but what's probably missing is an "rpath" specification when
linking plperl.so.  Did you build with --disable-rpath, perhaps?  That's
the usual recommendation when building for Linux, but if you want to use
any non-default libraries, you can't do it.

Go into src/pl/plperl, remove and remake plperl.so, and see whether the
link command includes anything like

-Wl,-rpath,'/usr/lib64/perl5/CORE',--enable-new-dtags

(That's what I see when building with a stock Linux Perl configuration and
rpath enabled.)  If there's no such switch, or if it doesn't point to
where the libperl.so that you want to use is, then there's your problem.

regards, tom lane


-- 
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] PG 10 and perl

>Andy Colson wrote:
>In PG 9.5 this worked fine, and compiling and running PG used the 5.26 version 
>of perl:
>root@firefly:/usr/local/pg95/lib/postgresql# ldd plperl.so
>   libperl.so => 
> /opt/perl5/perls/perl-5.26/lib/5.26.1/x86_64-linux-thread-multi/CORE/libperl.so
>  (0x7f81e8bde000)
>
>I started playing with 10, and cannot get it to use the right version of perl. 
> I'll only use the system version:
>root@firefly:/usr/local/pg10/lib/postgresql# ldd plperl.so
>   libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x7fc9c67d4000)
>
>But, here's the thing.  The config.log reports 5.26 version:
>
>configure:7752: checking for perl
>configure:7770: found /opt/perl5/perls/perl-5.26/bin/perl
>configure:7782: result: /opt/perl5/perls/perl-5.26/bin/perl
>configure:7804: using perl 5.26.1
>...


I'm about to go down this path to prepare for 10.0, but I haven't had time yet.

You don't show your configure command, but did you set PERL there as well as 
have it in your PATH? Here's what I've done in the past:

./configure --prefix=/opt/pg-9.5 --with-system-tzdata --with-perl 
PERL=/opt/perl/bin/perl

and it used our custom Perl just fine. If you did that, I'm not sure what else 
to say at the moment. I hope to get around to checking out RC1 early next week 
though and I'll be using 5.26 as well.

Hmm, given your report, I'll try to move it up my ToDo list to give myself more 
time. I'll report back here if I get it to work and haven't seen a reply by you 
about you getting it to work also.

Kevin


-- 
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] Speed of conversion from int to bigint

[Jehan-Guillaume (ioguix) de Rorthais]

On Wed, 27 Sep 2017 11:31:54 +0200
Tomas Vondra  wrote:

> On 09/27/2017 10:08 AM, Jonathan Moules wrote:
> > Hi,
> > (Postgres 9.5 and 9.6)
> > We have a table of about 650million rows. It's a partitioned table, with
> > two "child" tables. We want to change its primary key type from int to
> > bigint while retaining the current values.
> > 
> > We're using this:
> > 
> > ALTER TABLE dta.my_table ALTER column table_id TYPE bigint;
> > 
> > But it's taking a very long time, and locking the database. We're going
> > to need to do this in production as well, so a long-term table-lock
> > isn't workable.  
> 
> It's taking very long time, because it does a full-table rewrite while
> holding AccessExclusiveLock on it. Which is the strongest lock mode.
> 
> > Is there anything we can do to speed things up? How long is this likely
> > to take?
> >   
> 
> What you can do, is roughly this:
> 
> ---
> ALTER TABLE my_table ADD COLUMN new_table_id TYPE bigint;
> 
> -- do this in batches, so that a single transaction does not update
> -- all the rows
> UPDATE my_table SET new_table_id = table_id;

After or before each UPDATE in your batch, make sure to run a VACUUM on your
table, to keep bloat as low as possible.

Without vacuum, you will probably end up with a table up to twice bigger than
before the maintenance...And you'll have to handle this in another maintenance
period.

> -- build unique index on the new column
> CREATE INDEX CONCURRENTLY my_table_2_pkey ON my_table (new_table_id);

It misses an "UNIQUE" keyword :

  CREATE UNIQUE INDEX CONCURRENTLY my_table_2_pkey ON my_table (new_table_id);


Regards,
-- 
Jehan-Guillaume de Rorthais
Dalibo


-- 
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] pg_rewind copy so much data

[Michael Paquier]

On Fri, Sep 29, 2017 at 6:22 PM, Hung Phan  wrote:
> Thanks for your help. Do you have any more ideas about my case? I cannot
> find any reason for that. If pg_rewind just examines WAL for changed blocks
> from the common checkpoint, why did it copy all data ? As I understand, the
> information in WAL about changes cannot cover all data in the database.

The only thing I have in mind able to create this much amount of data
using this less WAL is a CREATE DATABASE using as template an existing
database. Based on the information you are giving here this is the
best guess I can do.
-- 
Michael


-- 
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] pg_rewind copy so much data

[Hung Phan]

Thanks for your help. Do you have any more ideas about my case? I cannot
find any reason for that. If pg_rewind just examines WAL for changed blocks
from the common checkpoint, why did it copy all data ? As I understand, the
information in WAL about changes cannot cover all data in the database.

On Fri, Sep 29, 2017 at 12:06 PM, Michael Paquier  wrote:

> On Fri, Sep 29, 2017 at 1:06 PM, Hung Phan  wrote:
> > I used tablespace to store data and it seems to be that pg_rewind copied
> > everthing in the tablespace. Today I found an article posted by you
> (Michael
> > Paquier) and you said that there was no tablespace support. If so, is
> there
> > anyway to work around ?
>
> I guess you mean that article:
> http://paquier.xyz/postgresql-2/postgres-module-highlight-
> pg_rewind-to-recycle-a-postgres-master-into-a-slave/
> This is from 2013 and this refers to the first iterations of the tool.
> Tablespaces are now fully supported in the upstream version, as well
> as in the version on github for 9.3 and 9.4. See this commit which
> added support for tablespaces:
> https://github.com/vmware/pg_rewind/commit/19fb09cdcac397048f7d723c037fe6
> a10299a278
>
> FWIW, I use it daily, and pg_rewind is able to detect correctly page
> deltas even on non-default tablespaces. Really.
> --
> Michael
>