Re: [GENERAL] Is PL-PGSQL interpreted or complied?

[Pavel Stehule]

2017-07-06 2:10 GMT+02:00 Tim Uckun :

> I am curious about the stored proc languages inside of postgres. When I
> write a stored proc is it compiled to some internal representation or just
> interpreted? How does this work with other languages?
>

The PLpgSQL proc are saved in original form - you can see the content of
system table pg_proc. Before saving a syntax checking is processed. When
PLpgSQL function is executed first time in session, then source code is
loaded from pg_proc, and parsed to AST (Abstract syntax tree). AST is
stored in session cache. Next, the AST is directly interpreted, repeatedly.


>
> Also would it be possible to extract PL-PGSQL into a standalone (albeit
> crippled) language? Is the interpreter/compiler modular like that?
>

Probably it is possible, but it requires some work - PLpgSQL is just glue
for SQL or for SQL expressions. There are not mathematical unit, there are
not some like basic library. All is shared with Postgres SQL engine.  This
feature is very specific for PLpgSQL. Every expression in PLpgSQL is
translated to SELECT - some simple SELECTs are executed in special mode,
faster, than usual queries.

PLpgSQL source code
https://github.com/postgres/postgres/tree/master/src/pl/plpgsql/src

Regards

Pavel



> Thanks.
>

Re: [GENERAL] Imperative Query Languages

[dandl]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jason Dusek



SQL is great and I am fully on board with the idea. Everywhere I go, I promote 
the greatness of SQL, of the relational model, and of Postgres. I didn’t write 
in so much to challenge SQL or pitch navigational databases, as to ask about 
examples of “pseudo-imperative” languages. 

Brief comments.

1.   LINQ has some of what you describe, and certainly provides some of the 
benefits you mention.

2.The Third Manifesto http://thethirdmanifesto.com specifies a pure 
relational language (a ‘better SQL’) in largely imperative terms, and there is 
an implementation https://reldb.org that is mainly imperative.

3.   My own project http://www.andl.org/ is functional rather than 
imperative but still fulfils the same purpose. On SQLite and Postgres, it 
generates SQL.

No, SQL is not great. It’s actually full of holes at every level, from basic 
language design to serious breaches of the relational model to monstrous 
incompatibilities between implementations. However, the concept of SQL is great 
(which is why it’s been so successful), and existing implementations have done 
extraordinarily well, all things considered. As they say, the good enough is 
the enemy of the great. SQL is here to stay.

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

 

 

Re: [GENERAL] Is PL-PGSQL interpreted or complied?

[John R Pierce]

On 7/5/2017 5:10 PM, Tim Uckun wrote:
I am curious about the stored proc languages inside of postgres. When 
I write a stored proc is it compiled to some internal representation 
or just interpreted?




plpgsql is interpreted directly, I don't believe its even pre-tokenized.


How does this work with other languages?


that varies with the language..  PLJava is compiled to java byte codes 
by the javac compiler even before its loaded (as you load the 
precompiled .jar file with the pljava loader),   plpython uses .pyc 
files, same as if python is run from the command line,  plperl is direct 
interpreted, same as perl normally.   embedded C is precompiled to 
machine language as you just load the DLL/SO files into postgres etc 
etc.


Also would it be possible to extract PL-PGSQL into a standalone 
(albeit crippled) language? Is the interpreter/compiler modular like that?



the interpreter *IS* SQL, which is the whole database server.   I don't 
think a standalone plpgsql without SQL would be of much use.



--
john r pierce, recycling bits in santa cruz



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

Re: Fwd: [GENERAL] Streaming replication bandwith per table

[Maeldron T.]

Thank you.

Maybe it would help, but recently I had another issue with the tables
having large arrays. I likely will redesign that part of the application,
and I’ll see if it helps as a side effect.



On Thu, Jun 22, 2017 at 5:55 AM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 6/21/17 22:04, Maeldron T. wrote:
> > * Logical replication is in 10.0 Beta 1. I might be oldschool but I
> > would install 10.1 or maybe 10.0.2 into production
>
> There are also other logical replication options such as pglogical and
> londiste.
>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Imperative Query Languages

[Jason Dusek]

On Wed, 5 Jul 2017 at 14:36 Peter Geoghegan p...@bowt.ie
 wrote:

However, the OP seemed to be describing something that maps imperative
> code to a declarative SQL query or something equivalent, which isn't
> quite the same thing. The declarative nature of SQL feels restrictive
> or at least unfamiliar to many programmers.
>
Yes, that is what I am describing.

SQL is great and I am fully on board with the idea. Everywhere I go, I
promote the greatness of SQL, of the relational model, and of Postgres. I
didn’t write in so much to challenge SQL or pitch navigational databases,
as to ask about examples of “pseudo-imperative” languages.

Fortress is the most noteworthy of these; but anyone who’s seen Haskell’s do
notation realizes there is some promise in the idea: the imperative
structure makes some programs much clearer, even in a pure functional
language.

IMV, what the OP describes wouldn't work well because it would
> superficially *appear* to not be restrictive in the way that some
> people dislike, but actually would be just as restrictive. The only
> way you could write it is by imagining what the SQL it produced looks
> like.

It’s not so much that SQL is restrictive, as that it is confusing in parts.
It is the same with regards to Haskell without do notation — the chained
lambdas boggle the mind. Another — very similar — idea is futures as an
approach to getting around callbacks — they create the appearance of a
linear sequence of execution, when some parts might be parallel; but this
turns out to be a benefit overall. Yet another is using while read x in
shell pipelines — an imperative structure, yet used to model stream
processing.

But this is perhaps neither here nor there: it seems there are no such
languages, no logic languages disguised as imperative languages. There are
only a very few functional languages disguised as imperative languages.
It’s an idea that has interested me for many a year — for the reasons that
Peter cites — and not having seen any language wholly organized in this
way, I thought I’d write to ask you all if you had seen any.

Kind Regards,

Jason
​

[GENERAL] Is PL-PGSQL interpreted or complied?

[Tim Uckun]

I am curious about the stored proc languages inside of postgres. When I
write a stored proc is it compiled to some internal representation or just
interpreted? How does this work with other languages?

Also would it be possible to extract PL-PGSQL into a standalone (albeit
crippled) language? Is the interpreter/compiler modular like that?

Thanks.

Re: [GENERAL] Imperative Query Languages

[Peter Geoghegan]

On Wed, Jul 5, 2017 at 7:02 AM, Merlin Moncure  wrote:
> Downthread, Tom mentions CODASYL, etc, but could have mentioned the
> big daddy, ISAM, and all it's various flavors.  Virtually all business
> code was written using it (and much of it still is) leading into the
> SQL era.   Code generally looked exactly like your example, except it
> was much more stupid looking being written in (generally) COBOL, and
> added in error handling, which is where the technique tends to break
> down.  SQL came about because some particularly smart people realized
> that programmers were writing the same boiler plate code over and over
> again and that perhaps access to data could be generalized and focused
> down to the real problem being solved.   This fortunately came about
> before "enterprise developers" and "enterprise tool vendors" were as
> organized as they are today and so was able to germinate into
> something incredibly useful...

To state the very obvious: If you assume for the sake of discussion
that the programmer of a hypothetical imperative query language is
infinitely capable and dedicated, and so is at least as capable as any
possible query optimizer, the optimizer still comes out ahead, because
it is capable of producing a different, better query plan as the
underlying data changes. Of course, it's also true that it's very hard
to beat the query optimizer under ideal conditions.

However, the OP seemed to be describing something that maps imperative
code to a declarative SQL query or something equivalent, which isn't
quite the same thing. The declarative nature of SQL feels restrictive
or at least unfamiliar to many programmers. What is often somehow
missed is that it's restrictive in a way that's consistent with how
the relational model is supposed to work. It seems hard to some
programmers because you have to formulate your query in terms of an
outcome, not in terms of a series of steps that can be added to
iteratively, as a snippet of code is written. It's very different to
something like bash, because it requires a little bit of up-front,
deliberate mental effort. And, because performance with many different
possible outputs matters rather a lot.

IMV, what the OP describes wouldn't work well because it would
superficially *appear* to not be restrictive in the way that some
people dislike, but actually would be just as restrictive. The only
way you could write it is by imagining what the SQL it produced looks
like. Or, if I've misunderstood his point, then it wouldn't work
because of the same reason that things like CODASYL are obsolete.

Some developers don't like SQL because they don't have a good
intuition for how the relational model works. While SQL does have some
cruft -- incidental complexity that's a legacy of the past -- any
language that corrected SQL's shortcomings wouldn't be all that
different to SQL, and so wouldn't help with this general problem. Quel
wasn't successful because it was only somewhat better than SQL was at
the time.

This is a conversation that I had a few times when I worked for
Heroku, with coworkers that weren't on the database team. They asked
similar questions. It took me a while to piece this together.

-- 
Peter Geoghegan


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

[GENERAL] (Might be a bug) locale issue while upgrading data directory from PostgreSQL 8.4 to 9.5

[Mayank Agrawal]

Hello,

I am upgrading PostgreSQL 8.4 to 9.5 on Windows. There is some issue
related to locale. Details are as follows:

The steps that I am following are:

1. Install PostgreSQL 9.5 (8.4 is already installed).

2. Migrate data from 8.4's data dir to 9.5's data dir using pg_upgrade
utility.

3. After successful upgrade, uninstall PostgreSQL 8.4

Here the issue is that PostgreSQL 8.4 could be installed with any locale
(English or French etc.) and to have successful data directory upgrade,
PostgreSQL 9.5 must be installed with the same locale as that of 8.4.

Supplying the output of query 'Show lc_collate' (executed on PostgreSQL
8.4) to PostgreSQL 9.5 installation leads to failed installation. It
generates the error 'specified locale is not supported'.

Here is the example:

Output (PostgreSQL 8.4) of query 'SHOW LC_COLLATE': English_United
States.1252

A. --locale "English_United States.1252"

While installing PostgreSQL 9.5, If we pass locale option as above,
installer aborts with an error 'specified locale is not supported'.

B. --locale "English, United States"

If we pass locale option as above, installation is successful and cluster
locale is set to 'English, United States'.

*Given that one has access to PostgreSQL 8.4, how would one know what value
to pass to the parameter --locale while installing PostgreSQL 9.5?*

Thanks and Regards,

Mayank Agrawal

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Adrian Klaver]

On 07/05/2017 08:31 AM, Hans Schou wrote:
2017-07-05 15:41 GMT+02:00 Adrian Klaver >:




[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]
^

The thing is that in a quick search on this I did not find a
reference implementation of this to compare against.


"dsn" is only relevant when the scheme is ODBC.


That was formatting error on my part, I was trying to point to the back 
half of the URI. The part with the table/column/sql sections.




In a situation where an application (like Drupal) get connect string 
(URI) it should be able to find the right driver to use. In case with of 
ODBC, a dsn (Data Source Name) is needed.


./hans




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


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

Re: [SPAM] [GENERAL] How to install pgadmin3 or pgadmin4 on linux machine

[PAWAN SHARMA]

On Wed, Jul 5, 2017 at 6:29 PM, Adrian Klaver 
wrote:

> On 07/05/2017 05:25 AM, PAWAN SHARMA wrote:
>
>>
>>
>
>> Hi Moreno,
>>
>>
>> Thanks for response.
>>
>> 1. We don't have yum working on servers
>> 2. We are using rpm to install postgres.
>>
>>
>> We downloading postgresql standard rpm from EDB site.
>>
>
> That is not what you said in your first post:
>
>
> "
> https://yum.postgresql.org/repopackages.php#pg95
>
> I have download pgdg-redhat95-9.5-3.noarch.rpm from the above link and
> install successfully on my server.
>
> "
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Hi All,

Thanks now it's working.

I have installed below rpms.

pgdg-redhat95-9.5-3.noarch
postgresql95-libs-9.5.7-1PGDG.rhel7.x86_64.rpm
postgresql95-9.5.7-1PGDG.rhel7.x86_64.rpm

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Tom Lane]

r...@bb-c.de (Rainer J.H. Brandt) writes:
> Tom Lane writes:
>> The whole thing's odd though --- certainly many people are building
>> PG successfully on macOS.  There's got to be something unusual about
>> Rainer's build environment, but what?

> I thought so, too, but I'm beginning to doubt it.  I'm now down to

>   ./configure --prefix=/opt/bb/170705

> and the initdb failure is the same.  The build machine is a few months old
> and has current OS and Xcode, and certainly no other build tools or other
> relevant stuff.  I have no special environment variables set.

Hmph.  I confess bafflement --- it works fine for me on curremt macOS.

After reading the clang & ld man pages for a bit I wondered if I could
reproduce the problem by adding -flto to the compiler switches.  I was
right that that causes "unused" functions to get stripped out of the
postgres executable, but then you get a pile of failures when these
libraries get built:

undef: _pg_mic2ascii
undef: _check_encoding_conversion_args
undef: _pg_ascii2mic
Undefined symbols for architecture x86_64:
  "_pg_mic2ascii", referenced from:
  _mic_to_ascii in lto.o
  "_check_encoding_conversion_args", referenced from:
  _ascii_to_mic in lto.o
  _mic_to_ascii in lto.o
  "_pg_ascii2mic", referenced from:
  _ascii_to_mic in lto.o
ld: symbol(s) not found for architecture x86_64
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make[3]: *** [ascii_and_mic.so] Error 1

Given that macOS's linker checks the resolvability of such symbols at
dylib build time, I really don't see how you could get through the
build successfully and then have a failure at run time.  It's weird.

Lacking any better ideas to offer, I can only suggest that maybe
removing/reinstalling Xcode would help.  (Hm, are you sure you've
installed Xcode's command line tools?  Try
xcode-select --install
to be sure.)

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_start/stop_backup non-exclusive scripts to snapshot

[Stephen Frost]

Melvin,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost  wrote:
> >Part of my concern is that such a script is unlikely to show any problems
> until it comes time to do a restore
> As previously stated, the script was used to set up a slave and has done so
> successfully many times. There are subsequent scripts
> that check results.

Ah, the impression was that this was being suggested as a way of
performing regular backups.  For simply creating a replica,
pg_basebackup works pretty well for small systems.  For larger
databases, being able to perform parallel backup/restore is very useful,
even if it's just for building a replica.

> >What might be worse would be to pull the plug while the backup is running
> and then try to bring the primary back online.
> Uh, whom would be so stupid as to do that?

Pulling the plug in the middle of various operations is a good way to
simulate what happens if the system crashes, for whatever reason, and to
make sure that processes and procedures are in place to address such a
failure scenario.

> >Right, there's little sense in trying to perfect a shell script when
> proper solutions exist.
> >>It's better to create something that others criticise than to create
> nothing and criticise others. Go create, have fun!!

Indeed, I'd certainly welcome criticism of pgBackRest.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

[Melvin Davidson]

On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost  wrote:

> Greetings,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > Stephen,
> > >This script is a good example of why trying to take a PG backup using
> > shell scripts isn't a good idea.
> >
> > Your criticism is noted, however, I have used it many times in the past
> > with absolutely no problem. I submitted that script as a possible
> solution
> > to the op's problem/question. If you have an alternate solution or can
> make
> > improvements to it, then I am sure the op and I would welcome them.
>
> Part of my concern is that such a script is unlikely to show any
> problems until it comes time to do a restore- it could be failing now
> due to the issues I noted previously without any obvious error being
> thrown but with the resulting backup not being viable.  Hopefully that
> isn't the case and ideally you're performing test restores of each
> backup you take to ensure that it works.
>
> Further, it doesn't address the OP's question, which was specifically
> how to avoid using the now-deprecated exclusive backup method that the
> script you posted uses.
>
> * Michael Paquier (michael.paqu...@gmail.com) wrote:
> > On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidson 
> wrote:
> > > Your criticism is noted, however, I have used it many times in the
> past with absolutely no problem.
> >
> > Plug off the server on which is stored the backup just after your
> > script finishes, you have a good chance to be surprised if you try to
> > restore from this backup later on.
>
> What might be worse would be to pull the plug while the backup is
> running and then try to bring the primary back online. :/  That issue is
> part of why the API used in this script is now deprecated.
>
> > > I submitted that script as a possible solution
> > > to the op's problem/question. If you have an alternate solution or can
> make improvements to it, then I am sure the op and I would welcome them.
> >
> > Stephen has mentioned two of them, with hundreds of man hours spent in
> developing those backup tools to be robust solutions, done by
> > specialists on the matter.
>
> Right, there's little sense in trying to perfect a shell script when
> proper solutions exist.
>
> Thanks!
>
> Stephen
>

>Part of my concern is that such a script is unlikely to show any problems
until it comes time to do a restore
As previously stated, the script was used to set up a slave and has done so
successfully many times. There are subsequent scripts
that check results.

>What might be worse would be to pull the plug while the backup is running
and then try to bring the primary back online.
Uh, whom would be so stupid as to do that?

>Right, there's little sense in trying to perfect a shell script when
proper solutions exist.
>>It's better to create something that others criticise than to create
nothing and criticise others. Go create, have fun!!

http://www.azquotes.com/quote/874849





-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Strange case of database bloat

[Jeff Janes]

On Tue, Jul 4, 2017 at 10:18 PM, Chris Travers 
wrote:

>
> Questions
> ===
> I assume that it is the fact that rows update frequently which is the
> problem here? But why doesn't Postgres re-use any of the empty disk pages?
>

Can you install the contrib extension pg_freespacemap and use "select *
from pg_freespace('table_name')" to see if PostgreSQL agrees that the space
is re-usable?

Cheers,

Jeff

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

2017-07-05 15:41 GMT+02:00 Adrian Klaver :

>
> [scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[
> dbname][/[[table[/[column[,column...]*]]]|sql]]]
> ^
>
> The thing is that in a quick search on this I did not find a reference
> implementation of this to compare against.
>

"dsn" is only relevant when the scheme is ODBC.

In a situation where an application (like Drupal) get connect string (URI)
it should be able to find the right driver to use. In case with of ODBC, a
dsn (Data Source Name) is needed.

./hans

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 05/07/2017 16:33, Adrian Klaver ha scritto:

On 07/05/2017 01:05 AM, Moreno Andreo wrote:

Il 04/07/2017 20:51, Daniel Verite ha scritto:

Tom Lane wrote:


Moreno Andreo  writes:
So the hint is to abandon manual COPY and let pg_dump do the hard 
work?
If it is a newline-conversion problem, compressed pg_dump archives 
would

be just as subject to corruption as your binary COPY file is.
It's mentioned in [1] that the signature at the beginning of these 
files

embed a CRLF to detect this newline-conversion problem early on,
so I would expect COPY IN to stumble on a corrupted signature
and abort earlier in the process, if that conversion occurred.
Instead the report says it fails after a number of tuples:
Given what you said, can I assume it's a file transfer or an 
hardware-driven (pendrive) problem?


Daniel also mentioned the harddrive as a possible source of error. I 
would say monitoring where and when the issues appear may help with 
determining the source.
Yeah, trying to restore the same file on another machine should help 
determine the possible culprit.




--
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] Re: Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

2017-07-05 16:29 GMT+02:00 Thomas Kellerer :

> Hans Schou schrieb am 05.07.2017 um 14:27:
> > The dburl (or dburi) has become common to use by many systems
> > connecting to a database. The feature is that one can pass all
> > parameters in a string, which has similar pattern as http-URI do.
> >
> > Especially when using psql in a script, having the credentials in one
> string is convenient.
> >
> > The syntax could be:
> >   [scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[
> dbname][/[[table[/[column[,column...]*]]]|sql]]]
>
> Besides the fact that something like that is already possible:
>
> What's the use of "table" and "column" in the URI? You connect to a
> database, not to a table.
>

With 'table' the idea was  to do a "SELECT * FROM table" on that.
With 'column' added only that or those columns would be listed.

The "sql" part in the end was supposed to be a SQL-statement to be
executed, like:
  psql "postgresql://localhost/dbname/INSERT INTO foo VALUES($RANDOM)"
which should be equal to
  echo "INSERT INTO foo VALUES($RANDOM)" | psql
postgresql://localhost/dbname

./hans

Re: [GENERAL] building extension with large string inserts

[David G. Johnston]

On Wed, Jul 5, 2017 at 8:04 AM, Tom van Tilburg 
wrote:

> I think I misunderstand. How would that help my insert statement?
> You would get INSERT INTO mytable VALUES ($ javascript with a lot of
> unescaped characters like /n " // etc. $);
>

​Tom Lane provided the relevant syntax link, though if you supply an
actual, shortened, example someone might show exactly what this all means
for you - or how you'd need to tweak your text to make it work.

$$ { "key1": "value"​,
   "key2": "value" } $$

Will be inserted as-is, explicit newlines and all.  Likewise,

$$ { "key1": "value", \n "key2": "value" } $$

will be inserted without any newlines and with a literal "\n" in the middle
of the text.

Unlike single-quote literals there is no alternate "E" form of
dollar-quoting that will cause the \n to be interpreted as a newline.  In
practice its absence doesn't seem missed.

David J.

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

2017-07-05 15:15 GMT+02:00 Albe Laurenz :

>
> Unless I misunderstand, this has been in PostgreSQL since 9.2:
>

Sorry! I did not read the *new* manual.
(OK, 9.2 is not that new)

It is even mentioned in the man page.

Then I have a new proposal. Write a note about in
  psql --help

./hans

Re: [GENERAL] building extension with large string inserts

[Tom van Tilburg]

You are right! I totally forgot about this dollar quoting :)
Typically one of those things you will only remember the hard way ;-)

Thanks a lot,
 Tom

On Wed, Jul 5, 2017 at 5:08 PM, Tom Lane  wrote:

> Tom van Tilburg  writes:
> > I think I misunderstand. How would that help my insert statement?
> > You would get INSERT INTO mytable VALUES ($ javascript with a lot of
> > unescaped characters like /n " // etc. $);
>
> Sure, but in a dollar-quoted literal you don't need to escape them.
>
> https://www.postgresql.org/docs/current/static/sql-
> syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
>
> > and: Am I correct that INSERTS are the way to go in extensions?
>
> Seems reasonable, if you want the extension script to be self-contained.
>
> regards, tom lane
>

Re: [GENERAL] building extension with large string inserts

[Tom Lane]

Tom van Tilburg  writes:
> I think I misunderstand. How would that help my insert statement?
> You would get INSERT INTO mytable VALUES ($ javascript with a lot of
> unescaped characters like /n " // etc. $);

Sure, but in a dollar-quoted literal you don't need to escape them.

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

> and: Am I correct that INSERTS are the way to go in extensions?

Seems reasonable, if you want the extension script to be self-contained.

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] building extension with large string inserts

[Tom van Tilburg]

I think I misunderstand. How would that help my insert statement?
You would get INSERT INTO mytable VALUES ($ javascript with a lot of
unescaped characters like /n " // etc. $);

and: Am I correct that INSERTS are the way to go in extensions?

Best,
 Tom vT.

On Wed, Jul 5, 2017 at 4:57 PM, Tom Lane  wrote:

> Tom van Tilburg  writes:
> > I am trying to build an extension where there is the need to insert large
> > strings consisting of javascript code.
> > ...
> > What would be a proper way to get this code into a table via an
> extension?
> > I've been working on generating INSERT statements for the extension's sql
> > file but it seems a tedious job to escape the code myself.
>
> Can't you use a dollar-quoted string?  You just need to pick a delimiter
> that doesn't appear anywhere in the javascript, say $JaVaScRiPt$ ...
>
> regards, tom lane
>

Re: [GENERAL] building extension with large string inserts

[Tom Lane]

Tom van Tilburg  writes:
> I am trying to build an extension where there is the need to insert large
> strings consisting of javascript code.
> ...
> What would be a proper way to get this code into a table via an extension?
> I've been working on generating INSERT statements for the extension's sql
> file but it seems a tedious job to escape the code myself.

Can't you use a dollar-quoted string?  You just need to pick a delimiter
that doesn't appear anywhere in the javascript, say $JaVaScRiPt$ ...

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] Invalid field size

[Adrian Klaver]

On 07/05/2017 01:05 AM, Moreno Andreo wrote:

Il 04/07/2017 20:51, Daniel Verite ha scritto:

Tom Lane wrote:


Moreno Andreo  writes:

So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.

It's mentioned in [1] that the signature at the beginning of these files
embed a CRLF to detect this newline-conversion problem early on,
so I would expect COPY IN to stumble on a corrupted signature
and abort earlier in the process, if that conversion occurred.
Instead the report says it fails after a number of tuples:
Given what you said, can I assume it's a file transfer or an 
hardware-driven (pendrive) problem?


Daniel also mentioned the harddrive as a possible source of error. I 
would say monitoring where and when the issues appear may help with 
determining the source.









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


--
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] 64bit initdb failure on macOS 10.11 and 10.12

[Adrian Klaver]

On 07/04/2017 11:56 PM, Rainer J.H. Brandt wrote:

Tom Lane writes:

Adrian Klaver  writes:

On 07/04/2017 01:29 PM, Rainer J.H. Brandt wrote:

Good to know.  I removed those options and tried again.



Did you run make clean before re-running ./configure?


Personally I do "make distclean" before changing any configure options.
I'm not sure how much difference that really makes, but why waste brain
cells chasing such issues?  Build cycles are cheap.

Right, and it's not the issue here.  I always do each build in a freshly
unpacked source tree.


The whole thing's odd though --- certainly many people are building
PG successfully on macOS.  There's got to be something unusual about
Rainer's build environment, but what?

I thought so, too, but I'm beginning to doubt it.  I'm now down to

   ./configure --prefix=/opt/bb/170705

and the initdb failure is the same.  The build machine is a few months old
and has current OS and Xcode, and certainly no other build tools or other
relevant stuff.  I have no special environment variables set.


I do not build on OS X so this is a bit of a reach for me, still here it 
goes. From your original post the error was:


"reating conversions ... FATAL:  could not load library 
"/opt/bb/170704/lib/postgresql/ascii_and_mic.so": 
dlopen(/opt/bb/170704/lib/postgresql/ascii_and_mic.so, 10): Symbol not 
found: _check_encoding_conversion_args

  Referenced from: /opt/bb/170704/lib/postgresql/ascii_and_mic.so
  Expected in: /opt/bb/170704/bin/postgres
 in /opt/bb/170704/lib/postgresql/ascii_and_mic.so"

You also mentioned you have done 32 bit builds that worked.

Now when I do 64 bit build on Linux the libraries end up in ~/lib64/:

/usr/local/pgsql/lib64> l ascii_and_mic.so
-rwxr-xr-x 1 root root 9760 Jun 14 07:32 ascii_and_mic.so*

Yet in your case I see ~/lib/. Not sure how OS X handles 32bit/64bit, 
which is where I am reaching. Just wondering if there is cross 
contamination going on?


Another thought, what does:

ldd ascii_and_mic.so

show?




It's been a while since I built PG on macOS, but I regularly do on other
operating systems, and haven't had any trouble in a very long time.

Rainer




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


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

[GENERAL] Re: Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Thomas Kellerer]

Hans Schou schrieb am 05.07.2017 um 14:27:
> The dburl (or dburi) has become common to use by many systems
> connecting to a database. The feature is that one can pass all
> parameters in a string, which has similar pattern as http-URI do.
>
> Especially when using psql in a script, having the credentials in one string 
> is convenient.
> 
> The syntax could be:
>   
> [scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]

Besides the fact that something like that is already possible: 

What's the use of "table" and "column" in the URI? You connect to a database, 
not to a table.

Thomas



-- 
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_start/stop_backup non-exclusive scripts to snapshot

[Stephen Frost]

Greetings,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> Stephen,
> >This script is a good example of why trying to take a PG backup using
> shell scripts isn't a good idea.
> 
> Your criticism is noted, however, I have used it many times in the past
> with absolutely no problem. I submitted that script as a possible solution
> to the op's problem/question. If you have an alternate solution or can make
> improvements to it, then I am sure the op and I would welcome them.

Part of my concern is that such a script is unlikely to show any
problems until it comes time to do a restore- it could be failing now
due to the issues I noted previously without any obvious error being
thrown but with the resulting backup not being viable.  Hopefully that
isn't the case and ideally you're performing test restores of each
backup you take to ensure that it works.

Further, it doesn't address the OP's question, which was specifically
how to avoid using the now-deprecated exclusive backup method that the
script you posted uses.

* Michael Paquier (michael.paqu...@gmail.com) wrote:
> On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidson  wrote:
> > Your criticism is noted, however, I have used it many times in the past 
> > with absolutely no problem.
> 
> Plug off the server on which is stored the backup just after your
> script finishes, you have a good chance to be surprised if you try to
> restore from this backup later on.

What might be worse would be to pull the plug while the backup is
running and then try to bring the primary back online. :/  That issue is
part of why the API used in this script is now deprecated.

> > I submitted that script as a possible solution
> > to the op's problem/question. If you have an alternate solution or can make 
> > improvements to it, then I am sure the op and I would welcome them.
> 
> Stephen has mentioned two of them, with hundreds of man hours spent in
> developing those backup tools to be robust solutions, done by
> specialists on the matter.

Right, there's little sense in trying to perfect a shell script when
proper solutions exist.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Strange case of database bloat

[Chris Travers]

On Wed, Jul 5, 2017 at 3:51 PM, Bill Moran  wrote:

> On Wed, 5 Jul 2017 13:28:29 +0200
> Chris Travers  wrote:
>
> > On Wed, Jul 5, 2017 at 1:00 PM, PT  wrote:
> >
> > > 2x the working size for a frequently updated table isn't terrible
> bloat.
> > > Or are
> > > you saying it grows 2x every 24 hours and keeps growing? The real
> question
> > > is
> > > how often the table is being vacuumed. How long have you let the
> > > experiment run
> > > for? Does the table find an equilibrium size where it stops growing?
> Have
> > > you
> > > turned on logging for autovacuum to see how often it actually runs on
> this
> > > table?
> >
> > If it were only twice it would not bother me.  The fact that it is twice
> > after 24 hrs, 3x after 48 hrs and 4x after 72 hrs is alarming.
>
> Ok, yup, that seems like an issue.
>
> > > No unremovable rows does not indicate that autovaccum is keeping up. It
> > > just
> > > indicates that you don't have a problem with uncommitted transactions
> > > holding
> > > rows for long periods of time.
> >
> > Right.  I should have specified that I also have not seen auto vacuum in
> > pg_stat_activity with an unusual duration.
>
> How long does it take when you run it manually? My experience is that
> autovac
> can take orders of magnitude longer with the default cost delays, but just
> becuase you don't see it, doesn't mean it's not happening. Turn on autovac
> logging and check the logs after a few days.
>

a few min for a normal vacuum, maybe 20-30 min for vacuum full (on one of
the large tables).

>
> > > Have you looked at tuning the autovacuum parameters for this table?
> More
> > > frequent
> > > vacuums should keep things more under control. However, if the write
> load
> > > is
> > > heavy, you'll probably want to lower autovacuum_vacuum_cost_delay.
> > > Personally,
> > > I feel like the default value for this should be 0, but there are
> likely
> > > those
> > > that would debate that. In any event, if that setting is too high it
> can
> > > cause
> > > autovacuum to take so long that it can't keep up. In theory, setting it
> > > too low
> > > can cause autovaccum to have a negative performance impact, but I've
> never
> > > seen
> > > that happen on modern hardware.
> >
> > Most of the writes are periodic (hourly?) batch updates which are fairly
> > big.
>
> Not sure how that statement is related to the comments I made preceeding
> it.
>

Not using cost-based vacuum afaik but will check that.  It is a good point.

>
> > > But that's all speculation until you know how frequently autovacuum
> runs on
> > > that table and how long it takes to do its work.
> >
> > Given the other time I have seen similar behaviour, the question in my
> mind
> > is why free pages near the beginning of the table don't seem to be
> re-used.
>
> It's possible that the early pages don't have enough usable space for the
> updated
> rows. Depending on your update patterns, you may end up with bloat
> scattered across
> many pages, with no individual page having enough space to be reused. That
> seems
> unlikely as the bloat becomes many times the used space, though.
>

The fire 35 pages are completely empty.  As I say I have seen this sort of
thing before (and I wonder if empty pages early in a table are somehow
biased against in terms of writes).

>
> The pg_freespacemap extension should be useful in determining if that's
> what's
> happening. Combine that with turning on logging to ensure that autovacuum
> is
> actually operating effectively.
>

I am not convinced it is a vacuum problem.  Would it be likely that large
batch updates would linearly continue to write pages forward as a
performance optimisation?


>
> --
> Bill Moran 
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Imperative Query Languages

[Merlin Moncure]

On Wed, Jul 5, 2017 at 12:22 AM, Jason Dusek  wrote:
> Hi All,
>
> This more of a general interest than specifically Postgres question. Are
> there any “semi-imperative” query languages that have been tried in the
> past? I’m imagining a language where something like this:

huh.  Somewhat snarky answer, all of them? :-).  Most languages are
imperative including just about all the ones that are popular in "the
enterprise" (minus SQL).   You can for example code that looks
remarkably like that in pl/pgsql.  Since postgres is a SQL server, it
would have to get compiled down to boring SQL statements but it's
generally understood (at least by me) that this is an inefficient way
to write code.

Downthread, Tom mentions CODASYL, etc, but could have mentioned the
big daddy, ISAM, and all it's various flavors.  Virtually all business
code was written using it (and much of it still is) leading into the
SQL era.   Code generally looked exactly like your example, except it
was much more stupid looking being written in (generally) COBOL, and
added in error handling, which is where the technique tends to break
down.  SQL came about because some particularly smart people realized
that programmers were writing the same boiler plate code over and over
again and that perhaps access to data could be generalized and focused
down to the real problem being solved.   This fortunately came about
before "enterprise developers" and "enterprise tool vendors" were as
organized as they are today and so was able to germinate into
something incredibly useful...

This revolution in programming was IMNSHO the single greatest
innovation in computer science; so much so that legions of the
unenlightened have been tirelessly working (hibernate) to eliminate or
displace its benefits, without much success:
http://www.codingdojo.com/blog/9-most-in-demand-programming-languages-of-2016/

:-D

merlin


-- 
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_start/stop_backup non-exclusive scripts to snapshot

[Michael Paquier]

On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidson  wrote:
> Your criticism is noted, however, I have used it many times in the past with 
> absolutely no problem.

Plug off the server on which is stored the backup just after your
script finishes, you have a good chance to be surprised if you try to
restore from this backup later on.

> I submitted that script as a possible solution
> to the op's problem/question. If you have an alternate solution or can make 
> improvements to it, then I am sure the op and I would welcome them.

Stephen has mentioned two of them, with hundreds of man hours spent in
developing those backup tools to be robust solutions, done by
specialists on the matter.
-- 
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] Strange case of database bloat

[Bill Moran]

On Wed, 5 Jul 2017 13:28:29 +0200
Chris Travers  wrote:

> On Wed, Jul 5, 2017 at 1:00 PM, PT  wrote:
> 
> > 2x the working size for a frequently updated table isn't terrible bloat.
> > Or are
> > you saying it grows 2x every 24 hours and keeps growing? The real question
> > is
> > how often the table is being vacuumed. How long have you let the
> > experiment run
> > for? Does the table find an equilibrium size where it stops growing? Have
> > you
> > turned on logging for autovacuum to see how often it actually runs on this
> > table?
> 
> If it were only twice it would not bother me.  The fact that it is twice
> after 24 hrs, 3x after 48 hrs and 4x after 72 hrs is alarming.

Ok, yup, that seems like an issue.

> > No unremovable rows does not indicate that autovaccum is keeping up. It
> > just
> > indicates that you don't have a problem with uncommitted transactions
> > holding
> > rows for long periods of time.
> 
> Right.  I should have specified that I also have not seen auto vacuum in
> pg_stat_activity with an unusual duration.

How long does it take when you run it manually? My experience is that autovac
can take orders of magnitude longer with the default cost delays, but just
becuase you don't see it, doesn't mean it's not happening. Turn on autovac
logging and check the logs after a few days.

> > Have you looked at tuning the autovacuum parameters for this table? More
> > frequent
> > vacuums should keep things more under control. However, if the write load
> > is
> > heavy, you'll probably want to lower autovacuum_vacuum_cost_delay.
> > Personally,
> > I feel like the default value for this should be 0, but there are likely
> > those
> > that would debate that. In any event, if that setting is too high it can
> > cause
> > autovacuum to take so long that it can't keep up. In theory, setting it
> > too low
> > can cause autovaccum to have a negative performance impact, but I've never
> > seen
> > that happen on modern hardware.
> 
> Most of the writes are periodic (hourly?) batch updates which are fairly
> big.

Not sure how that statement is related to the comments I made preceeding it.

> > But that's all speculation until you know how frequently autovacuum runs on
> > that table and how long it takes to do its work.
> 
> Given the other time I have seen similar behaviour, the question in my mind
> is why free pages near the beginning of the table don't seem to be re-used.

It's possible that the early pages don't have enough usable space for the 
updated
rows. Depending on your update patterns, you may end up with bloat scattered 
across
many pages, with no individual page having enough space to be reused. That seems
unlikely as the bloat becomes many times the used space, though.

The pg_freespacemap extension should be useful in determining if that's what's
happening. Combine that with turning on logging to ensure that autovacuum is
actually operating effectively. 

-- 
Bill Moran 


-- 
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_start/stop_backup non-exclusive scripts to snapshot

[Melvin Davidson]

Stephen,
>This script is a good example of why trying to take a PG backup using
shell scripts isn't a good idea.

Your criticism is noted, however, I have used it many times in the past
with absolutely no problem. I submitted that script as a possible solution
to the op's problem/question. If you have an alternate solution or can make
improvements to it, then I am sure the op and I would welcome them.


On Wed, Jul 5, 2017 at 9:10 AM, Stephen Frost  wrote:

> Greetings,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost 
> wrote:
> > > I'd recommend considering one of the existing PG backup tools which
> know
> > > how to properly perform WAL archiving and tracking the start/stop
> points
> > > in the WAL of the backup.  Trying to write your own using shell
> scripts,
> > > even with ZFS snapshots, isn't trivial.  If you trust the ZFS snapshot
> > > to be perfectly atomic across all filesystems/tablespaces used for PG,
> > > you could just take a snapshot and forget the rest- PG will do crash
> > > recovery when you have to restore from that snapshot but that's not
> much
> > > different from having to do WAL replay of the WAL generated during the
> > > backup.
> > >
> > > As for existing solutions, my preference/bias is for pgBackRest, but
> > > there are other options out there which also work, such as barman.
> >
> > Here is a model shell script I use to do a base backup to set up a slave.
> > See attached ws_base_backup.sh
>
> This script is a good example of why trying to take a PG backup using
> shell scripts isn't a good idea.  Offhand, there's issues like:
>
> - No check that start_backup was successful
> - No check that stop_backup was successful
> - No syncing of files to disk anywhere
> - Requires running as root (without any particular clear reason why)
> - Doesn't check if the database is already in 'exclusive backup' mode
> - Doesn't check the return codes for the main 'tar' command
> - Uses pipes without checking return codes through PIPESTATUS
> - Doesn't capture the output from pg_start/stop_backup
> - Doesn't verify that all of the WAL required for the backup was
>   archvied
> - Doesn't check the exit code of the rsync
>
> I'm sure there's other issues also and I do hope it's working enough
> that you have viable backups, but I wouldn't use such a script today
> (though I wrote plenty like it in the distant past).
>
> Thanks!
>
> Stephen
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Adrian Klaver]

On 07/05/2017 06:15 AM, Albe Laurenz wrote:

Hans Schou wrote:

The dburl (or dburi) has become common to use by many systems connecting to a 
database.
The feature is that one can pass all parameters in a string, which has similar 
pattern as
http-URI do.

Especially when using psql in a script, having the credentials in one string is
convenient.


The syntax could be:

[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,col
umn...]*]]]|sql]]]


Example of usage:
   psql pgsql://joe:p4zzw...@example.org:2345/dbname


[...]


I have attached an example of how it could be implemented. It uses libpcre 
RegEx to pass
the dburl.


Unless I misunderstand, this has been in PostgreSQL since 9.2:


T think the OP was referring to the latter part of:

[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]
^

The thing is that in a quick search on this I did not find a reference 
implementation of this to compare against.




Yours,
Laurenz Albe




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


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

[GENERAL] building extension with large string inserts

[Tom van Tilburg]

I am trying to build an extension where there is the need to insert large
strings consisting of javascript code. The easiest way to get these string
currently into a table is by using

\set varname `cat mycode.js`
INSERT INTO mytable VALUES (:'varname');

and run this from the psql client.
psql will escape the string nicely and stuff it into a text field.

This does not work with extensions since I cannot use \set anywhere else
than the psql client.

What would be a proper way to get this code into a table via an extension?
I've been working on generating INSERT statements for the extension's sql
file but it seems a tedious job to escape the code myself.

Best,
 Tom

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Albe Laurenz]

Hans Schou wrote:
> The dburl (or dburi) has become common to use by many systems connecting to a 
> database.
> The feature is that one can pass all parameters in a string, which has 
> similar pattern as
> http-URI do.
> 
> Especially when using psql in a script, having the credentials in one string 
> is
> convenient.
> 
> 
> The syntax could be:
> 
> [scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,col
> umn...]*]]]|sql]]]
> 
> 
> Example of usage:
>   psql pgsql://joe:p4zzw...@example.org:2345/dbname

[...]

> I have attached an example of how it could be implemented. It uses libpcre 
> RegEx to pass
> the dburl.

Unless I misunderstand, this has been in PostgreSQL since 9.2:

https://www.postgresql.org/docs/current/static/libpq-connect.html#AEN45571
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b035cb9db7aa7c0f28581b23feb10d3c559701f6

Yours,
Laurenz Albe

-- 
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_start/stop_backup non-exclusive scripts to snapshot

[Stephen Frost]

Greetings,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost  wrote:
> > I'd recommend considering one of the existing PG backup tools which know
> > how to properly perform WAL archiving and tracking the start/stop points
> > in the WAL of the backup.  Trying to write your own using shell scripts,
> > even with ZFS snapshots, isn't trivial.  If you trust the ZFS snapshot
> > to be perfectly atomic across all filesystems/tablespaces used for PG,
> > you could just take a snapshot and forget the rest- PG will do crash
> > recovery when you have to restore from that snapshot but that's not much
> > different from having to do WAL replay of the WAL generated during the
> > backup.
> >
> > As for existing solutions, my preference/bias is for pgBackRest, but
> > there are other options out there which also work, such as barman.
> 
> Here is a model shell script I use to do a base backup to set up a slave.
> See attached ws_base_backup.sh

This script is a good example of why trying to take a PG backup using
shell scripts isn't a good idea.  Offhand, there's issues like:

- No check that start_backup was successful
- No check that stop_backup was successful
- No syncing of files to disk anywhere
- Requires running as root (without any particular clear reason why)
- Doesn't check if the database is already in 'exclusive backup' mode
- Doesn't check the return codes for the main 'tar' command
- Uses pipes without checking return codes through PIPESTATUS
- Doesn't capture the output from pg_start/stop_backup
- Doesn't verify that all of the WAL required for the backup was
  archvied
- Doesn't check the exit code of the rsync

I'm sure there's other issues also and I do hope it's working enough
that you have viable backups, but I wouldn't use such a script today
(though I wrote plenty like it in the distant past).

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Christoph Moench-Tegeder]

## Hans Schou (hans.sc...@gmail.com):

> Example of usage:
>   psql pgsql://joe:p4zzw...@example.org:2345/dbname

Make the scheme "postgresql" and you're here:
https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
"32.1.1.2. Connection URIs".

Regards,
Christoph

-- 
Spare Space


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

Re: [SPAM] [GENERAL] How to install pgadmin3 or pgadmin4 on linux machine

[Adrian Klaver]

On 07/05/2017 05:25 AM, PAWAN SHARMA wrote:






Hi Moreno,


Thanks for response.

1. We don't have yum working on servers
2. We are using rpm to install postgres.


We downloading postgresql standard rpm from EDB site.


That is not what you said in your first post:

"
https://yum.postgresql.org/repopackages.php#pg95

I have download pgdg-redhat95-9.5-3.noarch.rpm from the above link and 
install successfully on my server.


"


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


--
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] [ADMIN] How to install pgadmin3 or pgadmin4 on linux machine

[Devrim Gündüz]

Hi,

On Wed, 2017-07-05 at 17:05 +0530, PAWAN SHARMA wrote:
> Please help me to install pgadmin3 or pgadmin4 on Redhat server.
> 
> 
> https://yum.postgresql.org/repopackages.php#pg95
> 
> I have download pgdg-redhat95-9.5-3.noarch.rpm from the above link and
> install successfully on my server.
> 
> what next how to configure it?

Run:

yum install pgadmin4-v1-web pgadmin4-v1 

to install pgadmin4. You can run pgadmin4 from command line, or use the GUI to
find it. 

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

[GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

Hi

The dburl (or dburi) has become common to use by many systems connecting to
a database. The feature is that one can pass all parameters in a string,
which has similar pattern as http-URI do.
Especially when using psql in a script, having the credentials in one
string is convenient.

The syntax could be:

[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]

Example of usage:
  psql pgsql://joe:p4zzw...@example.org:2345/dbname

Where
  Scheme: pgsql
  Username: joe
  Password: p4zzw0rd
  Host: example.org
  Port: 2345
  Database: dbname

I have attached an example of how it could be implemented. It uses libpcre
RegEx to pass the dburl.

best regards
Hans
diff -Naur /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.c ./dburl.c
--- /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.c   1970-01-01 
01:00:00.0 +0100
+++ ./dburl.c   2017-07-05 13:52:30.823234720 +0200
@@ -0,0 +1,261 @@
+/*
+ * Compile:
+ *   gcc -Wall -DUNIT_TEST dburl.c -lpcre -o dburl
+ *
+ * Test:
+ *   ./dburl 'pgsqls://example/' 
'pgsqls://username:password@host:5432/dbname/SELECT * FROM mytable'
+ *   ./dburl 'mysql://username:password@host:3306/dbname/table/column1,column2'
+ *   ./dburl 'odbc+dsn:table/column1,column2'
+ */
+
+//#define INCLUDE_COMMENTS 1
+
+#ifdef UNIT_TEST
+#include 
+#endif
+
+#include 
+#include 
+#include 
+#include 
+#include 
+#include "dburl.h"
+
+#define OVECCOUNT (50*3)
+
+#define IDX_SCHEME   1
+#define IDX_DSN  IDX_SCHEME+1
+#define IDX_USERNAME IDX_DSN+1
+#define IDX_PASSWORD IDX_USERNAME+1
+#define IDX_HOST IDX_PASSWORD+1
+#define IDX_PORT IDX_HOST+1
+#define IDX_DBNAME   IDX_PORT+1
+#define IDX_TABLEIDX_DBNAME+1
+#define IDX_COLUMN   IDX_TABLE+1
+#define IDX_SQL  IDX_COLUMN+1
+
+const char *schemeitems[] = {
+   "null",
+   "scheme",
+   "dsn",
+   "username",
+   "password",
+   "host",
+   "port",
+   "dbname",
+   "table",
+   "column",
+   "sql"
+};
+
+#ifdef INCLUDE_COMMENTS
+#define cm(msg) "(?#\n " msg "\n)"
+#else
+#define cm(msg)
+#endif
+
+const char syntaxdescription[] =
+"[sql:][scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]"
+;
+const char dburlregularexpression[] =
+"^"
+   cm("Optional prefix 'sql:'")
+   "(?:sql:)?"
+   cm("Scheme: pgsql")
+   "([-.a-z0-9]*)(?:[+]([-.a-z0-9]*))?"
+   cm("Required: URL identifier")
+   "://"
+   cm("Username + password")
+   "(?:"
+   cm("Username")
+   "([-a-z0-9_]+)"
+   cm("Password")
+   "(?::([^@]*))?@"
+   ")?"
+   cm("Hostname")
+   "("
+   cm("localhost | example")
+   "(?:[a-z0-9]+(?:-+[-a-z0-9]+)*)"
+   "|"
+   cm("Domain name with dot: example.com")
+   "(?:(?:[a-z0-9]+(?:-+[a-z0-9]+)*\\.)?"
+   "(?:[a-z0-9]+(?:-+[a-z0-9]+)*\\.)+(?:[a-z]{2,7})\\.?)"
+   "|"
+   cm("IPv4 number")
+   
"(?:(?:25[0-5]|2[0-4][0-9]|[0-1][0-9]{2}|[0-9][0-9]|[0-9])\\.){3}"
+   "(?:25[0-5]|2[0-4][0-9]|[0-1][0-9]{2}|[0-9][0-9]|[0-9])"
+   ")?"
+   cm("Port number: 3306|5432")
+   "(?::(\\d{1,5}))?"
+   cm("DB, table, SQL")
+   "(?:/"
+   "(?:"
+   cm("Dbname: joe|mydb, default $USER")
+   "(?:([_a-z0-9]+)?"
+   "(?:/"
+   "(?:"
+   cm("Table: mytable")
+   "(?:([_a-z0-9]+)"
+   cm("Columns: id, name")
+   "(?:/"
+   
"((?:[_a-z0-9]+)"
+   
"(?:,[_a-z0-9]+)*"
+   ")?"
+   ")?"
+   ")|("
+   cm("SQL: SELECT id, 
name FROM mytable")
+   "[^\\h]+\\h.+"
+   ")?"
+   ")?"
+   ")?"
+   ")?"
+   ")?"
+   ")?"
+"$"
+;
+
+static char *termstring(char *txt, int *ov, int idx, char *para_def) {
+   char *tmp = NULL;
+
+   /* if there is a match on this index... */
+   if (ov[2*idx+1] > 0) {
+   int length = ov[2*idx+1] - ov[2*idx];
+   if ((tmp = malloc(length+1))) {
+   strncpy(tmp, [ov[2*idx]], length);
+   tmp[length] = 0;
+   }
+   }
+   

Re: [SPAM] [GENERAL] How to install pgadmin3 or pgadmin4 on linux machine

[PAWAN SHARMA]

On Jul 5, 2017 17:40, "Moreno Andreo"  wrote:

Il 05/07/2017 13:35, PAWAN SHARMA ha scritto:

Hi All,

Please help me to install pgadmin3 or pgadmin4 on Redhat server.


https://yum.postgresql.org/repopackages.php#pg95

I have download pgdg-redhat95-9.5-3.noarch.rpm from the above link and
install successfully on my server.

what next how to configure it?

Using PostgreSQL Version : 9.5.7


-Pawan



Google is always your friend, the fourth result searching "install pgadmin3
on redhat server" returned

https://www.1337admin.org/linux/installing-pgadmin-on-rhel-7/

Cheers

Moreno


Hi Moreno,


Thanks for response.

1. We don't have yum working on servers
2. We are using rpm to install postgres.


We downloading postgresql standard rpm from EDB site.

Re: [SPAM] [GENERAL] How to install pgadmin3 or pgadmin4 on linux machine

[Moreno Andreo]

  
  
Il 05/07/2017 13:35, PAWAN SHARMA ha
  scritto:


  

  

  

  
Hi All,
  
  
  
  Please help me to install pgadmin3 or pgadmin4 on
Redhat server.
  
  
  
  
  https://yum.postgresql.org/repopackages.php#pg95
  
  
  
  I have download pgdg-redhat95-9.5-3.noarch.rpm from
  the above link and install successfully on my server.
  
  
  what next how to configure it?
  
  
  Using PostgreSQL Version : 9.5.7 
  
  
  
  
  -Pawan
  
  
  
  
  
  

  

  

  

Google is always your friend, the fourth result searching
  "install pgadmin3 on redhat server" returned

https://www.1337admin.org/linux/installing-pgadmin-on-rhel-7/
Cheers
Moreno



  

Re: [GENERAL] Strange case of database bloat

[Scott Mead]

On Wed, Jul 5, 2017 at 7:28 AM, Chris Travers 
wrote:

>
>
> On Wed, Jul 5, 2017 at 1:00 PM, PT  wrote:
>
>>
>> 2x the working size for a frequently updated table isn't terrible bloat.
>> Or are
>> you saying it grows 2x every 24 hours and keeps growing? The real
>> question is
>> how often the table is being vacuumed. How long have you let the
>> experiment run
>> for? Does the table find an equilibrium size where it stops growing? Have
>> you
>> turned on logging for autovacuum to see how often it actually runs on this
>> table?
>>
>
> If it were only twice it would not bother me.  The fact that it is twice
> after 24 hrs, 3x after 48 hrs and 4x after 72 hrs is alarming.
>
>>
>> No unremovable rows does not indicate that autovaccum is keeping up. It
>> just
>> indicates that you don't have a problem with uncommitted transactions
>> holding
>> rows for long periods of time.
>>
>
> Right.  I should have specified that I also have not seen auto vacuum in
> pg_stat_activity with an unusual duration.
>

What about anything 'WHERE state = 'idle in transaction' ?



>
>> Have you looked at tuning the autovacuum parameters for this table? More
>> frequent
>> vacuums should keep things more under control. However, if the write load
>> is
>> heavy, you'll probably want to lower autovacuum_vacuum_cost_delay.
>> Personally,
>> I feel like the default value for this should be 0, but there are likely
>> those
>> that would debate that. In any event, if that setting is too high it can
>> cause
>> autovacuum to take so long that it can't keep up. In theory, setting it
>> too low
>> can cause autovaccum to have a negative performance impact, but I've
>> never seen
>> that happen on modern hardware.
>>
>
> Most of the writes are periodic (hourly?) batch updates which are fairly
> big.
>

I've had similar issues when each update makes a row larger than any of the
available slots.  I had a workload (admittedly on an older version of
postgres) where we were updating every row a few times a day.  Each time,
the row (a bytea field) would grow about 0.5 - 5.0%.  This would prevent us
from using freespace (it was all too small).  The only way around this was :

1. Run manual table rebuilds (this was before pg_repack / reorg).  Use
pg_repack now
2. Fix the app

  Essentially, I would do targeted, aggressive vacuuming and then, once a
month (or once I hit a bloat threshold) do a repack (again, it was my
custom process back then).  This was the bandage until I could get the app
fixed to stop churning so badly.


>
>> But that's all speculation until you know how frequently autovacuum runs
>> on
>> that table and how long it takes to do its work.
>>
>
> Given the other time I have seen similar behaviour, the question in my
> mind is why free pages near the beginning of the table don't seem to be
> re-used.
>
> I would like to try to verify that however, if you have any ideas.
>
>>
>> --
>> PT 
>>
>
>
>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

[GENERAL] How to install pgadmin3 or pgadmin4 on linux machine

[PAWAN SHARMA]

Hi All,

Please help me to install pgadmin3 or pgadmin4 on Redhat server.


https://yum.postgresql.org/repopackages.php#pg95

I have download pgdg-redhat95-9.5-3.noarch.rpm from the above link and
install successfully on my server.

what next how to configure it?

Using PostgreSQL Version : 9.5.7


-Pawan

Re: [GENERAL] Strange case of database bloat

[Chris Travers]

On Wed, Jul 5, 2017 at 1:00 PM, PT  wrote:

>
> 2x the working size for a frequently updated table isn't terrible bloat.
> Or are
> you saying it grows 2x every 24 hours and keeps growing? The real question
> is
> how often the table is being vacuumed. How long have you let the
> experiment run
> for? Does the table find an equilibrium size where it stops growing? Have
> you
> turned on logging for autovacuum to see how often it actually runs on this
> table?
>

If it were only twice it would not bother me.  The fact that it is twice
after 24 hrs, 3x after 48 hrs and 4x after 72 hrs is alarming.

>
> No unremovable rows does not indicate that autovaccum is keeping up. It
> just
> indicates that you don't have a problem with uncommitted transactions
> holding
> rows for long periods of time.
>

Right.  I should have specified that I also have not seen auto vacuum in
pg_stat_activity with an unusual duration.

>
> Have you looked at tuning the autovacuum parameters for this table? More
> frequent
> vacuums should keep things more under control. However, if the write load
> is
> heavy, you'll probably want to lower autovacuum_vacuum_cost_delay.
> Personally,
> I feel like the default value for this should be 0, but there are likely
> those
> that would debate that. In any event, if that setting is too high it can
> cause
> autovacuum to take so long that it can't keep up. In theory, setting it
> too low
> can cause autovaccum to have a negative performance impact, but I've never
> seen
> that happen on modern hardware.
>

Most of the writes are periodic (hourly?) batch updates which are fairly
big.

>
> But that's all speculation until you know how frequently autovacuum runs on
> that table and how long it takes to do its work.
>

Given the other time I have seen similar behaviour, the question in my mind
is why free pages near the beginning of the table don't seem to be re-used.

I would like to try to verify that however, if you have any ideas.

>
> --
> PT 
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Strange case of database bloat

[PT]

On Wed, 5 Jul 2017 07:18:03 +0200
Chris Travers  wrote:

> Hi;
> 
> First, I haven't seen major problems of database bloat in a long time which
> is why I find this case strange.  I wanted to ask here what may be causing
> it.
> 
> Problem:
> ==
> Database is in the 100GB to 200GB size range, running on btrfs (not my
> choice) with nodatacow enabled (which I set up to fix a performance
> issue).  The workload is a very heavy batch-update workload.
> 
> The database bloats linearly.  I have measured this on one  table (of 149M
> rows).
> 
> After vacuum full this table is (including indexes): 17GB
> Every 24 hrs, seems to add its original space in size to the file system
> +/-.
> 
> Bloat seems to be affecting both indexes and underlying tables.
> 
> Vacuum verbose does not indicate a disproportionate number of rows being
> unremovable.  So autovacuum is keeping up without too much difficulty.
> 
> 
> Troubleshooting so far
> ===
> 
>  filefrag finds a single extent on each file, so copy-on-write is not the
> culprit
> 
> Selecting the smallest 10 values of ctid from one of the bloating tables
> shows the first page used is around page 35 with one row per used page (and
> large gaps in between).
> 
> Questions
> ===
> I assume that it is the fact that rows update frequently which is the
> problem here? But why doesn't Postgres re-use any of the empty disk pages?
> 
> More importantly, is there anything that can be done to mitigate this issue
> other than a frequent vacuum full?

2x the working size for a frequently updated table isn't terrible bloat. Or are
you saying it grows 2x every 24 hours and keeps growing? The real question is
how often the table is being vacuumed. How long have you let the experiment run
for? Does the table find an equilibrium size where it stops growing? Have you
turned on logging for autovacuum to see how often it actually runs on this
table?

No unremovable rows does not indicate that autovaccum is keeping up. It just
indicates that you don't have a problem with uncommitted transactions holding
rows for long periods of time.

Have you looked at tuning the autovacuum parameters for this table? More 
frequent
vacuums should keep things more under control. However, if the write load is
heavy, you'll probably want to lower autovacuum_vacuum_cost_delay. Personally,
I feel like the default value for this should be 0, but there are likely those
that would debate that. In any event, if that setting is too high it can cause
autovacuum to take so long that it can't keep up. In theory, setting it too low
can cause autovaccum to have a negative performance impact, but I've never seen
that happen on modern hardware.

But that's all speculation until you know how frequently autovacuum runs on
that table and how long it takes to do its work.

-- 
PT 


-- 
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] Invalid field size

[Moreno Andreo]

Il 04/07/2017 20:51, Daniel Verite ha scritto:

Tom Lane wrote:


Moreno Andreo  writes:

So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.

It's mentioned in [1] that the signature at the beginning of these files
embed a CRLF to detect this newline-conversion problem early on,
so I would expect COPY IN to stumble on a corrupted signature
and abort earlier in the process, if that conversion occurred.
Instead the report says it fails after a number of tuples:
Given what you said, can I assume it's a file transfer or an 
hardware-driven (pendrive) problem?




--
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] Imperative Query Languages

[Jason Dusek]

On Tue, 4 Jul 2017 at 23:57 Chris Travers chris.trav...@gmail.com
 wrote:

I am curious where you see LINQ as starting at an imperative syntax.
>
The imperative integration is thin, I admit — it just the integration with
for loops.

Here's a good case that illustrates the problem I think.  Suppose the
> following is understood imperatively:
>
> FOR x IN RANGE student
> SELECT WHERE x.age < 25
> PROJECT ALL(x), lock_if_possible(x.id)
>
> Now, lock_if_possible has side effects.  If we understand this to be
> imperative, then we have no possibility of turning this into a declarative
> query because we are interested in the side effects.  So you cannot say
> that this is equivalent to the SQL of
>
> SELECT *, lock_if_possible(id)
> FROM student
> WHERE age < 25
>
> The reason is that while the imperative version represents *one* valid
> interpretation of the declarative, there are other interpretations of the
> declarative that are not at all equivalent.  The hoops we have to jump
> through to make this work in an imperative way in SQL are sometimes rather
> amusing.
>
What are some alternative interpretations of this query? Are you referring
to which rows are candidates for locking? Or the order of locking?

Kind Regards,

Jason

​

Re: [GENERAL] Imperative Query Languages

[Chris Travers]

On Wed, Jul 5, 2017 at 8:42 AM, Jason Dusek  wrote:

>
>
> If we imagine network databases have one layer:
>
>   Imperative Plan
>
> And SQL databases have two:
>
>   Declarative Query -> Imperative Plan
>
> It seems reasonable to say, LINQ,  have three:
>

>   Imperative Syntax -> Declarative Query -> Imperative Plan
>
> Fortress is rather the same, since it translates imperative to functional
> to assembly.
>

I am curious where you see LINQ as starting at an imperative syntax.

Here's a good case that illustrates the problem I think.  Suppose the
following is understood imperatively:

FOR x IN RANGE student
SELECT WHERE x.age < 25
PROJECT ALL(x), lock_if_possible(x.id)

Now, lock_if_possible has side effects.  If we understand this to be
imperative, then we have no possibility of turning this into a declarative
query because we are interested in the side effects.  So you cannot say
that this is equivalent to the SQL of

SELECT *, lock_if_possible(id)
FROM student
WHERE age < 25

The reason is that while the imperative version represents *one* valid
interpretation of the declarative, there are other interpretations of the
declarative that are not at all equivalent.  The hoops we have to jump
through to make this work in an imperative way in SQL are sometimes rather
amusing.


>
> Kind Regards,
>   Jason
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Rainer J.H. Brandt]

Tom Lane writes:
> Adrian Klaver  writes:
> > On 07/04/2017 01:29 PM, Rainer J.H. Brandt wrote:
> >> Good to know.  I removed those options and tried again.
> 
> > Did you run make clean before re-running ./configure?
> 
> Personally I do "make distclean" before changing any configure options.
> I'm not sure how much difference that really makes, but why waste brain
> cells chasing such issues?  Build cycles are cheap.
Right, and it's not the issue here.  I always do each build in a freshly
unpacked source tree.

> The whole thing's odd though --- certainly many people are building
> PG successfully on macOS.  There's got to be something unusual about
> Rainer's build environment, but what?
I thought so, too, but I'm beginning to doubt it.  I'm now down to

  ./configure --prefix=/opt/bb/170705

and the initdb failure is the same.  The build machine is a few months old
and has current OS and Xcode, and certainly no other build tools or other
relevant stuff.  I have no special environment variables set.

It's been a while since I built PG on macOS, but I regularly do on other
operating systems, and haven't had any trouble in a very long time.

Rainer
-- 
Email: r...@bb-c.de
Telefon: 0172/9593205

Brandt & Brandt Computer GmbH
Am Wiesenpfad 6, 53340 Meckenheim
Geschäftsführer: Rainer J.H. Brandt und Volker A. Brandt
Handelsregister: Amtsgericht Bonn, HRB 10513


-- 
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] Strange case of database bloat

[Chris Travers]

On Wed, Jul 5, 2017 at 7:18 AM, Chris Travers 
wrote:

> Hi;
>
> First, I haven't seen major problems of database bloat in a long time
> which is why I find this case strange.  I wanted to ask here what may be
> causing it.
>
> Problem:
> ==
> Database is in the 100GB to 200GB size range, running on btrfs (not my
> choice) with nodatacow enabled (which I set up to fix a performance
> issue).  The workload is a very heavy batch-update workload.
>
> The database bloats linearly.  I have measured this on one  table (of 149M
> rows).
>
> After vacuum full this table is (including indexes): 17GB
> Every 24 hrs, seems to add its original space in size to the file system
> +/-.
>
> Bloat seems to be affecting both indexes and underlying tables.
>
> Vacuum verbose does not indicate a disproportionate number of rows being
> unremovable.  So autovacuum is keeping up without too much difficulty.
>
>
> Troubleshooting so far
> ===
>
>  filefrag finds a single extent on each file, so copy-on-write is not the
> culprit
>
> Selecting the smallest 10 values of ctid from one of the bloating tables
> shows the first page used is around page 35 with one row per used page (and
> large gaps in between).
>
> Questions
> ===
> I assume that it is the fact that rows update frequently which is the
> problem here? But why doesn't Postgres re-use any of the empty disk pages?
>
> More importantly, is there anything that can be done to mitigate this
> issue other than a frequent vacuum full?
>

Two points I think I forgot to mention:

This is PostgreSQL 9.5.1

Last I saw something similar was a more "minor" case on a larger db, on
PostgreSQL 9.3.x

The more minor case was a small table (maybe 20k rows) which had bloated to
1GB in size due to this same sort of problem but we ignored it because the
table was cached all the time and at the RAM we were using, it wasn't a
significant drain  on performance.  However, here it is.

First 20 CTIDs from one table:

(35,25)
(48,15)
(76,20)
(77,20)
(83,20)
(96,19)
(100,19)
(103,13)
(111,9)
(115,12)
(124,11)
(120,12)
(131,12)
(137,12)
(150,14)
(152,12)
(157,20)
(162,14)


> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Imperative Query Languages

[Jason Dusek]

On Tue, 4 Jul 2017 at 23:01 Tom Lane  wrote:

> I'm pretty sure that that is the model that relational databases (and the
> SQL language in particular) replaced, back in the 70s or so.  Look up
> "network" databases (eg CODASYL) and "hierarchical" DBs (eg IMS) for some
> ancient history here.  Yeah, you can do it like that, but it's seriously
> painful to develop and maintain.  People were more excited about spending
> human effort to save machine cycles forty years ago than they are today.


Network database programming is, indeed, imperative; but as I understand it
there was not much of a planning layer -- the program was the plan. In C#,
one has LINQ; and in Scala and Haskell, monadic comprehensions; and even in
Python one can overload iteration to allow a translation of imperative
syntax to declarative syntax. The goal with these features, is generally to
present a familiar interface to an unfamiliar semantics.

If we imagine network databases have one layer:

  Imperative Plan

And SQL databases have two:

  Declarative Query -> Imperative Plan

It seems reasonable to say, LINQ,  have three:

  Imperative Syntax -> Declarative Query -> Imperative Plan

Fortress is rather the same, since it translates imperative to functional
to assembly.

Kind Regards,
  Jason

Re: [GENERAL] Imperative Query Languages

[Chris Travers]

On Wed, Jul 5, 2017 at 8:34 AM, Jason Dusek  wrote:

>
>
> I can not argue these points with you; but Fortress is a good example of
> imperative looking code that translates to a functional/declarative core;
> as indeed is monadic or applicative code. LINQ is a more recent and
> widespread example -- though not encompassing an entire language -- of
> something that has an imperative form while being declarative under the
> hood. Scala's for comprehensions -- more or less monad comprehensions --are
> another.
>

But Linq effectively is a declarative language that's semi-SQL-like (I wish
they used "project" instead of "select" but that's another question).  I
don't see Linq as semi-imperative.

>
> With regards to Spark, I assume for comprehensions are an important part
> of the interface?
>

Nope.  You have chained generators and you really need to watch what is
parallelizable and what is not, and what is running on the partitions and
what is running post-gathering/shuffling.  Spark has no real facility for
parallelising a comprehension.

>
> Kind Regards,
>   Jason
>
>>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Imperative Query Languages

[Jason Dusek]

On Tue, 4 Jul 2017 at 23:22 Chris Travers  wrote:

> Having done a lot of SQL optimisation stuff  I have doubts that this is
> possible.  The problem is that it is much easier to go from a declarative
> to an imperative plan than it is to go the other way.  In fact sometimes we
> use SQL the way your first code works and then it is often a problem.
>
> For example, consider the difference between an EXISTS and an IN query, or
> between an INNER JOIN and a LATERAL JOIN.  PostgreSQL's optimiser is
> amazing at identifying cases where these are equivalent and planning
> accordingly, but it is extremely easy to get just outside the envelope
> where the optimiser gives up and has to default back to an imperative
> interpretation of these.  Proving that two imperative approaches are
> equivalent is a lot harder than proving that two different imperative
> approaches implement the same declarative request.  In other words, going
> imperative -> declarative strikes me as a far, far harder problem than the
> other way.
>
> Also I have done a little bit of work on Apache Spark and there it is
> extremely important to understand the imperative side of the data flow in
> that case (what is partitioned and what is not).
>

I can not argue these points with you; but Fortress is a good example of
imperative looking code that translates to a functional/declarative core;
as indeed is monadic or applicative code. LINQ is a more recent and
widespread example -- though not encompassing an entire language -- of
something that has an imperative form while being declarative under the
hood. Scala's for comprehensions -- more or less monad comprehensions --are
another.

With regards to Spark, I assume for comprehensions are an important part of
the interface?

Kind Regards,
  Jason

>

Re: [GENERAL] Imperative Query Languages

[Chris Travers]

On Wed, Jul 5, 2017 at 7:22 AM, Jason Dusek  wrote:

> Hi All,
>
> This more of a general interest than specifically Postgres question. Are
> there any “semi-imperative” query languages that have been tried in the
> past? I’m imagining a language where something like this:
>
> for employee in employees:
> for department in department:
> if employee.department == department.department and
>department.name == "infosec":
> yield employee.employee, employee.name, employee.location, 
> employee.favorite_drink
>
> would be planned and executed like this:
>
> SELECT employee.employee, employee.name, employee.location, 
> employee.favorite_drink
>   FROM employee JOIN department USING (department)
>  WHERE department.name == "infosec"
>
> The only language I can think of that is vaguely like this is Fortress, in
> that it attempts to emulate pseudocode and Fortran very closely while being
> fundamentally a dataflow language.
>
Having done a lot of SQL optimisation stuff  I have doubts that this is
possible.  The problem is that it is much easier to go from a declarative
to an imperative plan than it is to go the other way.  In fact sometimes we
use SQL the way your first code works and then it is often a problem.

For example, consider the difference between an EXISTS and an IN query, or
between an INNER JOIN and a LATERAL JOIN.  PostgreSQL's optimiser is
amazing at identifying cases where these are equivalent and planning
accordingly, but it is extremely easy to get just outside the envelope
where the optimiser gives up and has to default back to an imperative
interpretation of these.  Proving that two imperative approaches are
equivalent is a lot harder than proving that two different imperative
approaches implement the same declarative request.  In other words, going
imperative -> declarative strikes me as a far, far harder problem than the
other way.

Also I have done a little bit of work on Apache Spark and there it is
extremely important to understand the imperative side of the data flow in
that case (what is partitioned and what is not).

 --
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Imperative Query Languages

[Tom Lane]

Jason Dusek  writes:
> This more of a general interest than specifically Postgres question. Are
> there any “semi-imperative” query languages that have been tried in the
> past? I’m imagining a language where something like this:

> for employee in employees:
> for department in department:
> if employee.department == department.department and
>department.name == "infosec":
> yield employee.employee, employee.name, employee.location,
> employee.favorite_drink

I'm pretty sure that that is the model that relational databases (and the
SQL language in particular) replaced, back in the 70s or so.  Look up
"network" databases (eg CODASYL) and "hierarchical" DBs (eg IMS) for some
ancient history here.  Yeah, you can do it like that, but it's seriously
painful to develop and maintain.  People were more excited about spending
human effort to save machine cycles forty years ago than they are today.

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