Re: SQL equivalint of #incude directive ?

[Tony Shelver]

Similar to what Francisco said.  Not exactly sure what your use case is
though..

In the past I have usually used a DB modeling / design front end tool to
design my database, and then maintain and generate most of the build
scripts.
Datanamic Dezign used to be my go-to for SQL Server when i still paid money
for tools, and I maintained that as a central repository for all SQL
scripts for functions, tables, views, procedures and so on, and it would
generate create / diff scripts as necessary.

On Postgres I use pgmodeler, the only issue there is that I find it quicker
and easier to create procedures and functions and so on directly in code,
then have those in a script file that is run after the schema creation.
There are other (commercial) tools out there that will allow SQL script
preprocessing and generation.

I think you will find that most DBAs build their own scripts using tools
like Perl or Python, or a commercial product.

A similar situation exists for HTML, there is no standard pre-processor
directive.  I started off creating a Python utility to replace include
directives with input from external files, which worked fine.  Fairly
trivial if you are reasonably familiar with a scripting language.
Eventually i switched to an open source site generator that included that
capability along with a lot more functionality.



On Sat, 31 Aug 2019 at 10:12, Francisco Olarte 
wrote:

> Stan:
>
> On Fri, Aug 30, 2019 at 3:49 PM stan  wrote:
> ...
> > I am defining a bunch  of functions, and I would prefer to store them in
> a
> > separate file, which then gets "source" by the main DB init file.
> > Is there a standard way to do this?
>
> I assume you mean "standard for postgres", AFAIK there is not even a
> (multidb) standard way to feed an sql script to a server (content of
> queries / results are standard, how to send them / get them from the
> server is not ).
>
> \i in psql has been pointed. I do not know if this is one of your
> "workarounds", but what I normally do for this is trat the "init"
> script as a program and "make" it. I've done it using many of the
> preprocessor freely available around ( i.e. m4, cpp ) and also using a
> perl program ( but this is becuase I'm quite fluent in it and it makes
> some thing easier ), and even doing "cat *.sql | psql" and naming the
> scripts appropiately. You'll probably have it easier going by that
> route ( generating the sql script from chunks using a tool of your
> choice, it's just a big text chunk and you can easily debug it with a
> text editor ), and it is not that complex.
>
> Francisco Olarte.
>
>
>

Re: SQL equivalint of #incude directive ?

[Francisco Olarte]

Tony:

On Sun, Sep 1, 2019 at 10:05 AM Tony Shelver  wrote:
> I think you will find that most DBAs build their own scripts using tools like 
> Perl or Python, or a commercial product.

Tools like Perl are great for building text output, it's got a lot of
shortcuts for a lot of things. Not that sure about python. But I think
this are more used by developers wearing dba hats, or for dbas which
double duty as developer / sysadmins. Any dba needs some programming
skills, specially when dealing with dbs with procedure capabilities,
but not that much.

For a pure DBA I would recommend more things like a classic
preprocessor (I've got some success with m4, specially when using
m4sugar with it, and I'm sure there are better ones around there) or
some templating system. For just text CPP can be used, but it knows a
bit about C syntax which can become a nuisance, but for just includes
it can be fine.

> A similar situation exists for HTML, there is no standard pre-processor 
> directive.

IMO the reason is the same. SQL and HTML are not "source code", they
are the final product, the thing you send over an http connection ( in
html case ) or an implementation defined way ( i.e. inside the socket
connections psql makes using libpq ). They are like the window exes,
or the unix elf binaries. In the HTML case there are lots of
templating systems which you use to build the HTML content, in many
cases on the fly, and many of them can be reused easily for SQL.

I think the trick is not treating the SQL as the source, but as an EXE
file. Once you start thinking of it as such lots of things in your
usual toolbox become useful. I routinely do something similar for
configuration files, using just shell scripts full of echo+multiline
constant + source aux files + source some echoing files and, if quotes
are used for something like column names, cat + here docs.

Francisco Olarte.

pgmodeler ?

[stan]

I apologize, as this is a bit off topic for this list.

pgmodeler got mention in a thread that I started a day or two ago, and it looks
like it might be very  useful

I am running on Ubuntu 18.04 (latest STABLE), and I found that there is a 
package,
which I downloaded. The idea is to import the project I am working on.

But, when I try to import it I get an error message about:

t says that the column pr.proisaag.

I don't want to spend a lot of time trying to get this package to work, if it 
is a
dead end. I am using Postgres 11 if that matters.

Has anyone gotten this to work If so, can anyone point me to a quick HOWTO to
import my existing project?


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

Re: pgmodeler ?

[stan]

On Sun, Sep 01, 2019 at 06:30:23AM -0400, stan wrote:
> I apologize, as this is a bit off topic for this list.
> 
> pgmodeler got mention in a thread that I started a day or two ago, and it 
> looks
> like it might be very  useful
> 
> I am running on Ubuntu 18.04 (latest STABLE), and I found that there is a 
> package,
> which I downloaded. The idea is to import the project I am working on.
> 
> But, when I try to import it I get an error message about:
> 
> t says that the column pr.proisaag.
> 
> I don't want to spend a lot of time trying to get this package to work, if it 
> is a
> dead end. I am using Postgres 11 if that matters.
> 
> Has anyone gotten this to work If so, can anyone point me to a quick HOWTO to
> import my existing project?
> 
This is the specific error message:

Could not execute the SQL command.
Message returned: ERROR: column pr.proisagg does not exist
LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag...
^
HINT: Perhaps you meant to reference the column

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

Re: pgmodeler ?

[Pavel Stehule]

Hi

ne 1. 9. 2019 v 12:35 odesílatel stan  napsal:

> On Sun, Sep 01, 2019 at 06:30:23AM -0400, stan wrote:
> > I apologize, as this is a bit off topic for this list.
> >
> > pgmodeler got mention in a thread that I started a day or two ago, and
> it looks
> > like it might be very  useful
> >
> > I am running on Ubuntu 18.04 (latest STABLE), and I found that there is
> a package,
> > which I downloaded. The idea is to import the project I am working on.
> >
> > But, when I try to import it I get an error message about:
> >
> > t says that the column pr.proisaag.
> >
> > I don't want to spend a lot of time trying to get this package to work,
> if it is a
> > dead end. I am using Postgres 11 if that matters.
> >
> > Has anyone gotten this to work If so, can anyone point me to a quick
> HOWTO to
> > import my existing project?
> >
> This is the specific error message:
>
> Could not execute the SQL command.
> Message returned: ERROR: column pr.proisagg does not exist
> LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag...
> ^
> HINT: Perhaps you meant to reference the column
>

looks like pgmodeler doesn't support  PostgreSQL 11. proisagg was replaced
by prokind column

https://dba.stackexchange.com/questions/238903/postgresql-11-error-column-p-proisagg-does-not-exist

The application (pgmodeler) should be fixed.

Regards

Pavel

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

Re: pgmodeler ?

[Olivier Gautherot]

On Sun, Sep 1, 2019 at 6:35 AM stan  wrote:

> On Sun, Sep 01, 2019 at 06:30:23AM -0400, stan wrote:
> > I apologize, as this is a bit off topic for this list.
> >
> > pgmodeler got mention in a thread that I started a day or two ago, and
> it looks
> > like it might be very  useful
> >
> > I am running on Ubuntu 18.04 (latest STABLE), and I found that there is
> a package,
> > which I downloaded. The idea is to import the project I am working on.
> >
> > But, when I try to import it I get an error message about:
> >
> > t says that the column pr.proisaag.
> >
> > I don't want to spend a lot of time trying to get this package to work,
> if it is a
> > dead end. I am using Postgres 11 if that matters.
> >
> > Has anyone gotten this to work If so, can anyone point me to a quick
> HOWTO to
> > import my existing project?
> >
> This is the specific error message:
>
> Could not execute the SQL command.
> Message returned: ERROR: column pr.proisagg does not exist
> LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag...
> ^
> HINT: Perhaps you meant to reference the column
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>

Can you confirm the versions of pgModeler and PG? Pgmodeler 0.91 does not
fully support PG 11.x and there is an update in preparation in 0.92 which
should. You may want to givi it a try.

Cheers
--
Olivier Gautherot
https://www.linkedin.com/in/ogautherot/


Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: pgmodeler ?

[Thiemo Kellner]

 Quoting Olivier Gautherot :


This is the specific error message:


Could not execute the SQL command.
Message returned: ERROR: column pr.proisagg does not exist
LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag...
^
HINT: Perhaps you meant to reference the column

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


 
Can you confirm the versions of pgModeler and PG? Pgmodeler 0.91  
does not fully support PG 11.x and there is an update in preparation  
in 0.92 which should. You may want to givi it a try.

 


Actually, this is a known problem:  
https://github.com/pgmodeler/pgmodeler/issues/1281


Maybe you want to give the beta a shot: https://pgmodeler.io/download

Kind regards

Thiemo
 S/MIME Public Key:
https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B


smime.p7s
Description: S/MIME Signature

Re: Unexpected "canceling statement due to user request" error

[Tom Lane]

Will Storey  writes:
> Thanks for the pointer!

> It looks like we'd have to be entering that section and finding the
> statement timeout not set, otherwise we'd not fall through to the "user
> request" case.

> But as far as I can tell, the only reason my query would be cancelled is
> because of the statement timeout. I wonder if there is some way the
> statement timeout flag is being lost/cleared. I see there's situations
> where we clear timeout flags.

I poked at this for awhile and concluded that what you must be seeing is
that the statement timeout interrupt triggers, but no CHECK_FOR_INTERRUPTS
call happens thereafter, until we get to the disable_statement_timeout()
call in finish_xact_command().  That throws away the timeout-happened
indicator, but it does nothing to reset InterruptPending or
QueryCancelPending.  Those flags will also be thrown away eventually,
after we've read a new client command:

/*
 * (4) disable async signal conditions again.
 *
 * Query cancel is supposed to be a no-op when there is no query in
 * progress, so if a query cancel arrived while we were idle, just
 * reset QueryCancelPending. ProcessInterrupts() has that effect when
 * it's called when DoingCommandRead is set, so check for interrupts
 * before resetting DoingCommandRead.
 */
CHECK_FOR_INTERRUPTS();
DoingCommandRead = false;

Ignoring the timeout if it happened too late is what we're supposed
to be doing, so that's fine so far.

But: what happens if some other CHECK_FOR_INTERRUPTS occurs before we
reach the main input loop?  Of course, exactly the symptom you saw:
a bogus "canceling statement due to user request" error report.
What's worse, that happens after we've completed the transaction, leaving
the client with a very misleading idea of whether the command committed
or not.

So how could that happen?  After experimenting, I found a pretty
obvious candidate: statement duration logging happens after we
do finish_xact_command, and there's a CHECK_FOR_INTERRUPTS in
errfinish().  So I hypothesize that you have log_duration or
log_min_duration_statement enabled.

I was able to replicate the problem by lobotomizing a couple of
CHECK_FOR_INTERRUPTS that would normally happen in a simple
"select pg_sleep()" query (see attached for-demonstration-
purposes-only patch).  Then I get

regression=# set statement_timeout TO '1s';
SET
regression=# select pg_sleep(2.0);  -- completes normally after 2 sec
 pg_sleep 
--
 
(1 row)

regression=# set log_min_duration_statement TO '100ms';
SET
regression=# select pg_sleep(2.0);  -- sits for 2 sec, then:
ERROR:  canceling statement due to user request

So that's not very good and we ought to do something about it.
We can't have disable_statement_timeout() clear QueryCancelPending,
because that might lose state about some other cause for query
cancel.  I'm inclined to think that we need to split QueryCancelPending
into more than one flag.

A separate question is how come the particular query you're complaining
about has (seemingly) a fairly wide window where it never does any
CHECK_FOR_INTERRUPTS call before terminating.  Perhaps there's someplace
we need to sprinkle a few more of those.

regards, tom lane

diff --git a/src/backend/executor/nodeResult.c b/src/backend/executor/nodeResult.c
index 80ed9cc..d09ac95 100644
--- a/src/backend/executor/nodeResult.c
+++ b/src/backend/executor/nodeResult.c
@@ -72,7 +72,7 @@ ExecResult(PlanState *pstate)
 	PlanState  *outerPlan;
 	ExprContext *econtext;
 
-	CHECK_FOR_INTERRUPTS();
+//	CHECK_FOR_INTERRUPTS();
 
 	econtext = node->ps.ps_ExprContext;
 
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index d330a88..9f515db 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -369,7 +369,7 @@ pg_sleep(PG_FUNCTION_ARGS)
 		float8		delay;
 		long		delay_ms;
 
-		CHECK_FOR_INTERRUPTS();
+//		CHECK_FOR_INTERRUPTS();
 
 		delay = endtime - GetNowFloat();
 		if (delay >= 600.0)

Re: Unexpected "canceling statement due to user request" error

[Will Storey]

On Sun 2019-09-01 19:46:19 -0400, Tom Lane wrote:
> I poked at this for awhile and concluded that what you must be seeing is
> that the statement timeout interrupt triggers, but no CHECK_FOR_INTERRUPTS
> call happens thereafter, until we get to the disable_statement_timeout()
> call in finish_xact_command().  That throws away the timeout-happened
> indicator, but it does nothing to reset InterruptPending or
> QueryCancelPending.  Those flags will also be thrown away eventually,
> after we've read a new client command:
> 
> /*
>  * (4) disable async signal conditions again.
>  *
>  * Query cancel is supposed to be a no-op when there is no query in
>  * progress, so if a query cancel arrived while we were idle, just
>  * reset QueryCancelPending. ProcessInterrupts() has that effect when
>  * it's called when DoingCommandRead is set, so check for interrupts
>  * before resetting DoingCommandRead.
>  */
> CHECK_FOR_INTERRUPTS();
> DoingCommandRead = false;
> 
> Ignoring the timeout if it happened too late is what we're supposed
> to be doing, so that's fine so far.
> 
> But: what happens if some other CHECK_FOR_INTERRUPTS occurs before we
> reach the main input loop?  Of course, exactly the symptom you saw:
> a bogus "canceling statement due to user request" error report.
> What's worse, that happens after we've completed the transaction, leaving
> the client with a very misleading idea of whether the command committed
> or not.
> 
> So how could that happen?  After experimenting, I found a pretty
> obvious candidate: statement duration logging happens after we
> do finish_xact_command, and there's a CHECK_FOR_INTERRUPTS in
> errfinish().  So I hypothesize that you have log_duration or
> log_min_duration_statement enabled.

Yes, I have log_min_duration_statement set to 0! (log_duration is off)

> I was able to replicate the problem by lobotomizing a couple of
> CHECK_FOR_INTERRUPTS that would normally happen in a simple
> "select pg_sleep()" query (see attached for-demonstration-
> purposes-only patch).  Then I get
> 
> regression=# set statement_timeout TO '1s';
> SET
> regression=# select pg_sleep(2.0);  -- completes normally after 2 sec
>  pg_sleep 
> --
>  
> (1 row)
> 
> regression=# set log_min_duration_statement TO '100ms';
> SET
> regression=# select pg_sleep(2.0);  -- sits for 2 sec, then:
> ERROR:  canceling statement due to user request
> 
> So that's not very good and we ought to do something about it.
> We can't have disable_statement_timeout() clear QueryCancelPending,
> because that might lose state about some other cause for query
> cancel.  I'm inclined to think that we need to split QueryCancelPending
> into more than one flag.
> 
> A separate question is how come the particular query you're complaining
> about has (seemingly) a fairly wide window where it never does any
> CHECK_FOR_INTERRUPTS call before terminating.  Perhaps there's someplace
> we need to sprinkle a few more of those.

Yeah, it is strange. There are many queries in this system with statement
timeouts and this is the only one where I see this and it happens several
times a day.

Thank you so much for looking into this. An impressive analysis!

Re: Unexpected "canceling statement due to user request" error

[Tom Lane]

Will Storey  writes:
> On Sun 2019-09-01 19:46:19 -0400, Tom Lane wrote:
>> A separate question is how come the particular query you're complaining
>> about has (seemingly) a fairly wide window where it never does any
>> CHECK_FOR_INTERRUPTS call before terminating.  Perhaps there's someplace
>> we need to sprinkle a few more of those.

> Yeah, it is strange. There are many queries in this system with statement
> timeouts and this is the only one where I see this and it happens several
> times a day.

It'd be interesting to see EXPLAIN ANALYZE output from a typical execution
of that query.  Even better would be a test case (w/ tables and dummy
data) so somebody else could reproduce it; but maybe looking at EXPLAIN
ANALYZE would be enough to determine where we're missing checks.

regards, tom lane

Upgrade 96 -> 11

[James Sewell]

Hi,

I'm in the process of upgrading from 96 -> 11 (on RHEL 7.3) . Both the
versions have PostGIS 2.5.1 installed and working.

pg_upgrade fails with:

pg_restore: [archiver (db)] Error from TOC entry 440; 1259 537086 TABLE
tablename databasename
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.spatial_ref_sys" does not exist
LINE 39: "location_pt" "public"."geography"(Point,4283),

On looking further at the sequence of events I can see that:

   1. The PostGIS extension is created (but somehow the related tables such
   as spatial_ref_sys do not get created)
   2. The tablename table gets created causing the above error
   3. At some point later in the upgrade  spatial_ref_sys is to be created


Is there any way round this type of issue (I guess forcing spatial_ref_sys to
be created either with the extension as normal or just before any tables
which rely on it).

Cheers,
James Sewell,

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: How to get RAISE messges displayed?

[Luca Ferrari]

On Sat, Aug 31, 2019 at 12:35 AM stan  wrote:
> Got it working.
>
> Not 100% sure what I had wrong.

You can also do, in your session:
set client_min_messages to notice;
without having to change it in the configuration (for all sessions).

Luca