[GENERAL] Why can't the database owner create schemas and how can I enable that?

[Tim Uckun]

I am setting up a database for an app. So I create a user without superuser
but with login priveleges

I then create a database and set it's owner to that user like this...

dropdb --if-exists api_development
dropuser --if-exists api_user

createuser api_user -P -d
createdb api_development -O api_user

The app can now connect to the database but it can't create any tables,
schemas or anything else unless I give it superuser privileges.

Is there any way I can make this user a superuser for this database without
making it a superuser on other databases?

Re: [GENERAL] Is float8 a reference type?

[Tom Lane]

Paul A Jungwirth  writes:
> On Fri, Sep 22, 2017 at 8:05 PM, Pavel Stehule  
> wrote:
>> I don't think so it is good idea to write 64bit only extensions.

> I agree, but how about this?:

"Premature optimization is the root of all evil".  Do you have good reason
to think that it's worth your time to write unsafe/unportable code?  Do
you know that your compiler doesn't turn Float8GetDatum into a no-op
already?  (Mine does, on a 64-bit machine.)

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] Is float8 a reference type?

[Pavel Stehule]

2017-09-23 5:10 GMT+02:00 Paul A Jungwirth :

> On Fri, Sep 22, 2017 at 8:05 PM, Pavel Stehule 
> wrote:
> > yes, it is 8 bytes on 64-bit.
>
> Thanks!
>
> > I don't think so it is good idea to write 64bit only extensions.
>
> I agree, but how about this?:
>
> if (FLOAT8PASSBYVAL) {
>   datums = (Datum *)floats;
> } else {
>   datums = palloc0(arrlen * sizeof(Datum));
>   for (i = 0; i < arrlen; i++) {
> datums[i] = Float8GetDatum(floats[i]);
>   }
> }
>

it can work.

You have to solve deallocation in only one path. palloc0 is not necessary
in this case.


>
> Thanks,
> Paul
>

Re: [GENERAL] Is float8 a reference type?

[Paul A Jungwirth]

On Fri, Sep 22, 2017 at 8:05 PM, Pavel Stehule  wrote:
> yes, it is 8 bytes on 64-bit.

Thanks!

> I don't think so it is good idea to write 64bit only extensions.

I agree, but how about this?:

if (FLOAT8PASSBYVAL) {
  datums = (Datum *)floats;
} else {
  datums = palloc0(arrlen * sizeof(Datum));
  for (i = 0; i < arrlen; i++) {
datums[i] = Float8GetDatum(floats[i]);
  }
}

Thanks,
Paul


-- 
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] Is float8 a reference type?

[Pavel Stehule]

2017-09-23 4:52 GMT+02:00 Paul A Jungwirth :

> The docs say that a Datum can be 4 bytes or 8 depending on the machine:
>
> https://www.postgresql.org/docs/9.5/static/sql-createtype.html
>
> Is a Datum always 8 bytes for 64-bit architectures?
>
> And if so, can my C extension skip a loop like this when compiling
> there, and just do a memcpy (or even a cast)?:
>

yes, it is 8 bytes on 64-bit.

I don't think so it is good idea to write 64bit only extensions.



> float8 *floats;
> Datum *datums;
>
> datums = palloc(arrlen * sizeof(Datum));
> for (i = 0; i < arrlen; i++) {
>   datums[i] = Float8GetDatum(floats[i]);
> }
>
> Thanks!
> Paul
>
>
> --
> 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] Is float8 a reference type?

[Paul A Jungwirth]

On Fri, Sep 22, 2017 at 7:52 PM, Paul A Jungwirth
 wrote:
> Is a Datum always 8 bytes for 64-bit architectures?

Never mind, I found this in `pg_config.h`:

/* float8, int8, and related values are passed by value if 'true', by
   reference if 'false' */
#define FLOAT8PASSBYVAL true

Paul


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

[GENERAL] Is float8 a reference type?

[Paul A Jungwirth]

The docs say that a Datum can be 4 bytes or 8 depending on the machine:

https://www.postgresql.org/docs/9.5/static/sql-createtype.html

Is a Datum always 8 bytes for 64-bit architectures?

And if so, can my C extension skip a loop like this when compiling
there, and just do a memcpy (or even a cast)?:

float8 *floats;
Datum *datums;

datums = palloc(arrlen * sizeof(Datum));
for (i = 0; i < arrlen; i++) {
  datums[i] = Float8GetDatum(floats[i]);
}

Thanks!
Paul


-- 
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] Multicolumn Index on OR conditions

[legrand legrand]

No

You should try with 2 single column indexes and may be rewrite your query
with a Union All syntax




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


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

[GENERAL] errors on COMMIT and transaction states

[Andrei Matei]

Hello all,

I've got a question about the state in which a session/transaction finds
itself in case a COMMIT statement fails. I hope this is a good mailing list
to ask it on.
Is it true that a failed COMMIT behaves just like a ROLLBACK statement
would have if it was issues in its stead? In other words, is it true that
the transaction is rolled back (as it is on every error) and the session
will accept new statements without the need for the client to issue a
ROLLBACK?

And to extend the question a bit:
Is is true that, if a client uses the "simple query" flavor of the pgwire
protocol and sends multiple SQL commands in a single query string looking
like "BEGIN; ; ;...;COMMIT" then, when the result(s) come
and there was an error somewhere, the session can be in either of two
states:
- in an error state if the error was encountered by one of stmt1, stmt2,
etc. Meaning that future statements sent on future query strings will be
rejected with "ERROR:  current transaction is aborted, commands ignored
until end of transaction block" and the client needs to send a ROLLBACK to
get itself out of this sticky situation?
- in a regular state, accepting new statements, if the error was
encountered by COMMIT.

If everything I've said before is true, is there any guidance / best
practices that developers and/or client drivers should implement for
dealing with this "should I send a ROLLBACK so I can continue to use my
connection or not?" question when receiving an error after sending a
multi-statement query string that contains a COMMIT in it?

Thank you very much,

- Andrei

[GENERAL] shared_buffers smaller than max_wal_size

[Vladimir Mihailenco]

Hi,

I wonder what is the point of setting max WAL size bigger than shared
buffers, e.g.

shared_buffers = 512mb
max_wal_size = 2gb

As I understand a checkpoint happens after 2gb of data were modified
(writter to WAL), but shared buffers can contain at most 512mb of dirty
pages to be flushed to the disk. Is it still a win or I am missing
something?

Re: [GENERAL] VM-Ware Backup of VM safe?

[George Neuner]

On Fri, 22 Sep 2017 16:14:23 +0200, "Klaus P. Pieper"
 wrote:

>I am aware that VSS is purely Windows, and your comment about VSS aware
>application is true. Backup programs / VM managers like Data Protection
>Manager trigger these applications to put the files into a safe state
>prior to the snapshot.

The standard snapshot mechanism suspends all processes while the
memory is saved.  The writable disk image swap occurs in the
hypervisor and is transparent to the VM.

Data Protection adds the ability to snapshot on demand for backups and
signal aware programs in the VM to save state before being suspended.
WRT shapshot integrity, I don't think the latter really adds much, but
YMMV.

George



-- 
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] a JOIN to a VIEW seems slow

[Frank Millman]

Merlin Moncure wrote:

On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman  wrote:
>
> SELECT q.cust_row_id,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
> ) AS "balance_curr AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_30 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30'
> THEN q.balance ELSE 0 END
> ) AS "balance_60 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_90 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
> ) AS "balance_120 AS [DECTEXT]"
> FROM
> (SELECT
> due_trans.cust_row_id,
> due_trans.tran_date,
> trans_due.amount_cust +
> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)
> AS balance
> FROM prop.ar_trans_due trans_due
> LEFT JOIN prop.ar_trans due_trans ON
> due_trans.tran_type = trans_due.tran_type
> AND due_trans.tran_row_id = trans_due.tran_row_id
> WHERE due_trans.tran_date <= '2015-09-30'
> ) AS q
> GROUP BY q.cust_row_id
> ORDER BY q.cust_row_id;


> What is the performance with this portion simplified out?

> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)

> Change that to just '0' and rerun the query.  If timings are good, I
> think we want to explore converting this to LATERAL type join.  I
> think (but am not sure) this is defeating the optimizer.   Also, is
> this the actual query you want to run quickly?  You are not filtering
> on cust_row_id?


It makes a big difference – the query runs in 0.18 seconds.

This query can be used to return the age analysis for a single debtor or for 
all debtors, so yes I would sometimes run it without filtering.

A couple of comments -

1. My app supports 3 databases, PostgreSQL, SqlServer, and SQLite3. I am trying 
to keep my SQL as generic as possible. However, if I have to use something that 
is PostgreSQL-specific, I may have to live with that.

2. This is probably irrelevant but here is the query plan that SQLite3 creates -

3|0|0|SCAN TABLE ar_tran_inv
4|0|0|SCAN TABLE ar_tran_crn
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|0|SCAN TABLE ar_tran_rec
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN TABLE ar_trans_due AS trans_due
0|1|1|SEARCH SUBQUERY 1 AS due_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
9|0|0|SCAN TABLE ar_tran_inv
10|0|0|SCAN TABLE ar_tran_crn
8|0|0|COMPOUND SUBQUERIES 9 AND 10 (UNION ALL)
11|0|0|SCAN TABLE ar_tran_rec
7|0|0|COMPOUND SUBQUERIES 8 AND 11 (UNION ALL)
6|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
6|1|1|SEARCH SUBQUERY 7 AS alloc_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 12
15|0|0|SCAN TABLE ar_tran_inv
16|0|0|SCAN TABLE ar_tran_crn
14|0|0|COMPOUND SUBQUERIES 15 AND 16 (UNION ALL)
17|0|0|SCAN TABLE ar_tran_rec
13|0|0|COMPOUND SUBQUERIES 14 AND 17 (UNION ALL)
12|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
12|1|1|SEARCH SUBQUERY 13 AS alloc_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 18
21|0|0|SCAN TABLE ar_tran_inv
22|0|0|SCAN TABLE ar_tran_crn
20|0|0|COMPOUND SUBQUERIES 21 AND 22 (UNION ALL)
23|0|0|SCAN TABLE ar_tran_rec
19|0|0|COMPOUND SUBQUERIES 20 AND 23 (UNION ALL)
18|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
18|1|1|SEARCH SUBQUERY 19 AS alloc_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 24
27|0|0|SCAN TABLE ar_tran_inv
28|0|0|SCAN TABLE ar_tran_crn
26|0|0|COMPOUND SUBQUERIES 27 AND 28 (UNION ALL)
29|0|0|SCAN TABLE ar_tran_rec
25|0|0|COMPOUND SUBQUERIES 26 AND 29 (UNION ALL)
24|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
24|1|1|SEARCH 

Re: [GENERAL] VM-Ware Backup of VM safe?

[Klaus P. Pieper]

> -Ursprüngliche Nachricht-
> Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] Im Auftrag von George Neuner
> Gesendet: Donnerstag, 21. September 2017 18:35
>
> I can't speak for all VM managers, but Vmware's standard static
snapshots
> *do* capture both the memory and power states of the machine.  If a
snapshot
> is taken while a machine is running, restarting from that snapshot is
the same
> as if the machine woke up from suspension.

Thanks for clarification.

>
>
> >I may be wrong, but my understanding of a VSS writer is that all
> >transaction and log files are flushed to disk prior tot he snapshot.
>
> You understanding is correct - but I think you are maybe misapplying it
to this
> case.  VSS operates in the *host*, not in the virtual machine.  And VSS
is purely
> a Windows mechanism - it does not apply in Unix or Linux.

[...]

I am aware that VSS is purely Windows, and your comment about VSS aware
application is true. Backup programs / VM managers like Data Protection
Manager trigger these applications to put the files into a safe state
prior to the snapshot. As far as I am aware, MS SQL server provides this
mechanism while PostgreSQL does not. And for this reason, I would
certainly encourage using PostgreSQL's backup mechanisms for business
critical applications.

I just spoke with several admins who were not aware of these differences
between several databases.

Klaus





-- 
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] a JOIN to a VIEW seems slow

[Merlin Moncure]

On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman  wrote:
> On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
>>
>> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman 
>> > wrote:
>> >
>>
>> I did not get any response to this, but I am still persevering, and feel
>>
>> that I am getting closer. Instead of waiting 26 minutes for a result, I
>>
>> realise that I can learn a lot by using EXPLAIN. This is what I have found
>>
>> out.
>>
>
>
>>
> Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
> query?
>>
>
>
> I’m working on it, but my machine is playing up and it is getting late, so I
> will try again tomorrow.
>
> A passing comment – the 26 minute query is more complex, so will need some
> explaining (no pun intended). I was hoping that my simplified example would
> illustrate what I think is the problem.
>
> Anyway, here is the query -
>
> SELECT q.cust_row_id,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
> ) AS "balance_curr AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_30 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30'
> THEN q.balance ELSE 0 END
> ) AS "balance_60 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_90 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
> ) AS "balance_120 AS [DECTEXT]"
> FROM
> (SELECT
> due_trans.cust_row_id,
> due_trans.tran_date,
> trans_due.amount_cust +
> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)
> AS balance
> FROM prop.ar_trans_due trans_due
> LEFT JOIN prop.ar_trans due_trans ON
> due_trans.tran_type = trans_due.tran_type
> AND due_trans.tran_row_id = trans_due.tran_row_id
> WHERE due_trans.tran_date <= '2015-09-30'
> ) AS q
> GROUP BY q.cust_row_id
> ORDER BY q.cust_row_id;


What is the performance with this portion simplified out?

> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)

Change that to just '0' and rerun the query.  If timings are good, I
think we want to explore converting this to LATERAL type join.  I
think (but am not sure) this is defeating the optimizer.   Also, is
this the actual query you want to run quickly?  You are not filtering
on cust_row_id?

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] Insert large number of records

[Alban Hertroys]

On 20 September 2017 at 22:55, Job  wrote:
> One further question: within a query launched on the MASTER table where i 
> need to scan every table, for exaple to search rows locatd in more partitions.
> In there a way to improve "parallel scans" between more table at the same 
> time or not?
> I noticed, with explain analyze, the scan in the master table is Always 
> sequential, descending into the partitions.

Since nobody has replied to your latest question yet, I'll give it a try.

Which tables a query on your MASTER table needs to scan largely
depends on a PG feature called "constraint exclusion". That is to say,
if the query optimizer can deduce from your query that it only needs
to scan certain partitions for the required results, then it will do
so.

Now, whether the optimizer can do that, depends on whether your query
conditions contain the same (or equivalent) expressions on the same
fields of the same types as your partitioning constraints.

That 'same type' part is one that people easily miss. Sometimes part
of an expression gets auto-cast to make it compatible with the
remainder of the expression, but that is sometimes not the same type
as what is used in your partitioning (exclusion) constraint. In such
cases the planner often doesn't see the similarity between the two
expressions and ends up scanning the entire set of partitions.

See also section 5.10.4 in
https://www.postgresql.org/docs/current/static/ddl-partitioning.html ,
although it doesn't go into details of how to construct your select
statements to prevent scanning the entire partition set.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
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] Performance appending to an array column

[Imre Samu]

>I also tried cstore_fdw for this, but my queries
>(building a 2-D histogram) were taking 4+ seconds,
>compared to 500ms  using arrays.
> ...
> but maybe I could write my own extension

Have you checked the new TimescaleDB extension? [
https://github.com/timescale/timescaledb ]
"TimescaleDB is packaged as a PostgreSQL extension and released under the
Apache 2 open-source license."

"TimescaleDB is an open-source database designed to make SQL scalable for
time-series data.
It is engineered up from PostgreSQL, providing automatic partitioning
across time and space (partitioning key), as well as full SQL support."

and it has a built in histogram function:
https://docs.timescale.com/latest/api/api-timescaledb#histogram

Regards,
Imre





2017-09-21 23:05 GMT+02:00 Paul A Jungwirth :

> > It's going to suck big-time :-(.
>
> Ha ha that's what I thought, but thank you for confirming. :-)
>
> > We ended up keeping
> > the time series data outside the DB; I doubt the conclusion would be
> > different today.
>
> Interesting. That seems a little radical to me, but I'll consider it
> more seriously now. I also tried cstore_fdw for this, but my queries
> (building a 2-D histogram) were taking 4+ seconds, compared to 500ms
> using arrays. Putting everything into regular files gives up filtering
> and other SQL built-ins, but maybe I could write my own extension to
> load regular files into Postgres arrays, sort of getting the best of
> both worlds.
>
> Anyway, thanks for sharing your experience!
>
> Yours,
> Paul
>
>
> --
> 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: Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

[mike davis]

>This works for me:
>
>DO $$
>DECLARE
 > v_msg TEXT := 'SOMETHING IS WRONG';
>  v_sqlstate TEXT := 'E0001';
>BEGIN
>  RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate;
>EXCEPTION
>  WHEN SQLSTATE 'E0001' THEN
> RAISE NOTICE '%','Error E0001 raised - going to do something about it';
>  WHEN OTHERS THEN
> RAISE NOTICE 'OTHER ERRORS: %,%', sqlstate,sqlerrm;
>END$$;

>NOTICE:  Error E0001 raised - going to do something about it
>
>Or you could do
>  RAISE EXCEPTION SQLSTATE v_sqlstate USING message = v_msg;

That does indeed work !

The second possible way of :
RAISE EXCEPTION SQLSTATE v_sqlstate USING message = v_msg;

however doesn't ! I think that was the format i had also tried and why i went 
down the dymanic route.

So it seems variables can be used in the USING subclause but not outside it. 
The manual does seem to hint at this as
"after level if any, you can write a format (which must be a simple string 
literal, not an expression)"

Anyway,  RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate; works a 
treat!

Many thanks Tom & Pavel.

Mike

Re: [GENERAL] Question about SELECT statements with subselects

[Miloslav Semler]

Thank you very much. You have been right. Adding tramecky_id IS NOT NULL 
solved the problem.


Cheers,

Miloslav
Dne 22.9.2017 v 09:32 Albe Laurenz napsal(a):

Miloslav Semler wrote:

I found strange behavior with subselects and I am not able to explain
it. I have several tables in schema:

tramecky, mt_hodnoty, plata_kusy

in these tables, id is always primary key (serial), table_id is always
foreign key to table. When I run this query:

select tramecky.id FROM a.tramecky WHERE
  id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
  expedicni_plato IS NULL

I get 55 rows.

When I run this query:

select tramecky.id FROM a.tramecky WHERE
  id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
  id NOT IN(SELECT tramecky_id FROM a.mt_hodnoty) AND
  expedicni_plato IS NULL

I get no rows.. so I expect that rows with foreign keys tramecky_id of
55 rows are present in table mt_hodnoty. However result of query:

select mt_hodnoty.id FROM a.mt_hodnoty WHERE tramecky_id NOT IN(SELECT
tramecky_id FROM a.plata_kusy)

is empty set. Can anybody explain such strange behavior?

There is probably one or more rows in "mt_hodnoty" where "tramecky_id" is NULL.

Then the subselect
SELECT tramecky_id FROM a.mt_hodnoty
contains a NULL values, and the NOT IN clause will result in NULL,
which is not TRUE, so the result set is empty.

The NULL value does not show up in your second query, because
the condition NULL NOT IN (...) is also always NULL.

Yours,
Laurenz Albe


--
Technolog
Crytur, spol. s r.o.
Palackého 175
51101 Turnov



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

[GENERAL] Question about SELECT statements with subselects

[Miloslav Semler]

Hello,

I found strange behavior with subselects and I am not able to explain 
it. I have several tables in schema:


tramecky, mt_hodnoty, plata_kusy

in these tables, id is always primary key (serial), table_id is always 
foreign key to table. When I run this query:


select tramecky.id FROM a.tramecky WHERE
id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
expedicni_plato IS NULL

I get 55 rows.

When I run this query:

select tramecky.id FROM a.tramecky WHERE
id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
id NOT IN(SELECT tramecky_id FROM a.mt_hodnoty) AND
expedicni_plato IS NULL

I get no rows.. so I expect that rows with foreign keys tramecky_id of 
55 rows are present in table mt_hodnoty. However result of query:


select mt_hodnoty.id FROM a.mt_hodnoty WHERE tramecky_id NOT IN(SELECT 
tramecky_id FROM a.plata_kusy)


is empty set. Can anybody explain such strange behavior?

Thanks in advance,

Miloslav Semler


--
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] libpq confusion

[Thomas Delrue]

On Wednesday, September 20, 2017 1:47:05 PM EDT Igor Korot wrote:
>Thx.
>So it is referring to the command not a "command returning no data". ;-)

assuming
create table t (c int);

select c from t;
- PQresultStatus(result) == PGRES_TUPLES_OK
- PQntuples(result) == number or rows returned (int)
insert into t(c)values(1);
- PQresultStatus(result) ==PGRES_COMMAND_OK
- PQcmdTuples(result) == "1" (note: char*)
insert into t(c)values(1) returning c;
- PQresultStatus(result) ==PGRES_TUPLES_OK
- PQntuples(result) == 1 (int)

>On Wed, Sep 20, 2017 at 1:42 PM, John R Pierce  wrote:
>> On 9/20/2017 10:34 AM, Igor Korot wrote:
>> >From the documentation:
>> https://www.postgresql.org/docs/9.1/static/libpq-exec.html
>> 
>> [quote]
>> PGRES_COMMAND_OK
>> 
>> Successful completion of a command returning no data.
>> [/quote]
>> 
>> No data = no rows, right?
>> 
>> from that same page, a bit farther down, clarifying the potentially
>> confusing wording.
>> 
>> If the result status is PGRES_TUPLES_OK, then the functions described below
>> can be used to retrieve the rows returned by the query. Note that a SELECT
>> command that happens to retrieve zero rows still shows PGRES_TUPLES_OK.
>> PGRES_COMMAND_OK is for commands that can never return rows (INSERT,
>> UPDATE,
>> etc.). A response of PGRES_EMPTY_QUERY might indicate a bug in the client
>> software.


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

Re: [GENERAL] Question about SELECT statements with subselects

[Albe Laurenz]

Miloslav Semler wrote:
> I found strange behavior with subselects and I am not able to explain
> it. I have several tables in schema:
> 
> tramecky, mt_hodnoty, plata_kusy
> 
> in these tables, id is always primary key (serial), table_id is always
> foreign key to table. When I run this query:
> 
> select tramecky.id FROM a.tramecky WHERE
>  id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
>  expedicni_plato IS NULL
> 
> I get 55 rows.
> 
> When I run this query:
> 
> select tramecky.id FROM a.tramecky WHERE
>  id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
>  id NOT IN(SELECT tramecky_id FROM a.mt_hodnoty) AND
>  expedicni_plato IS NULL
> 
> I get no rows.. so I expect that rows with foreign keys tramecky_id of
> 55 rows are present in table mt_hodnoty. However result of query:
> 
> select mt_hodnoty.id FROM a.mt_hodnoty WHERE tramecky_id NOT IN(SELECT
> tramecky_id FROM a.plata_kusy)
> 
> is empty set. Can anybody explain such strange behavior?

There is probably one or more rows in "mt_hodnoty" where "tramecky_id" is NULL.

Then the subselect
   SELECT tramecky_id FROM a.mt_hodnoty
contains a NULL values, and the NOT IN clause will result in NULL,
which is not TRUE, so the result set is empty.

The NULL value does not show up in your second query, because
the condition NULL NOT IN (...) is also always NULL.

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

[GENERAL] Question about SELECT statements with subselects

[Miloslav Semler]

Hello,

I found strange behavior with subselects and I am not able to explain 
it. I have several tables in schema:


tramecky, mt_hodnoty, plata_kusy

in these tables, id is always primary key (serial), table_id is always 
foreign key to table. When I run this query:


select tramecky.id FROM a.tramecky WHERE
id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
expedicni_plato IS NULL

I get 55 rows.

When I run this query:

select tramecky.id FROM a.tramecky WHERE
id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
id NOT IN(SELECT tramecky_id FROM a.mt_hodnoty) AND
expedicni_plato IS NULL

I get no rows.. so I expect that rows with foreign keys tramecky_id of 
55 rows are present in table mt_hodnoty. However result of query:


select mt_hodnoty.id FROM a.mt_hodnoty WHERE tramecky_id NOT IN(SELECT 
tramecky_id FROM a.plata_kusy)


is empty set. Can anybody explain such strange behavior?

Thanks in advance,

Miloslav Semler



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