Re: Require assistance in Postgres + Hibernate : Error: java.math.BigInteger cannot be cast to java.math.BigDecimal

[Francisco Olarte]

Vinodh:

On Tue, Feb 13, 2018 at 9:58 AM, Vinodh NV  wrote:
> Require assistance on the below:
> Code snippet:
> Map ic;
> //Populate values for ic
> long count = ((BigDecimal)ic.get(“EB”)).longValue();
>
>
> Getting the below error:
> java.math.BigInteger cannot be cast to java.math.BigDecimal
> ®java.lang.ClassCastException: java.math.BigInteger cannot be cast to
> java.math.BigDecimal
>
> Since this is not part of a query doing select cast (EB as bigint) will not
> help in this case:

I do not know hibernate, so Ihaven't the sligstest idea of why
get("EB") returns BigInteger or why you do not cast it to BigInteger
instead of BigDecimal.

But if your problem is it sometimes has Bigdecimal and sometimes
BigInteger you could try casting it to the parent, Number, which is
the one specifying longValue() and would make the code work with both.



Francisco Olarte.

Multiple postmasters running from same directory

[Vikas Sharma]

Hi,

We are running Postgresql 9.4 with streaming replication and repmgr.
Operating system is RHEL6.8

On the master I can see multiple postmaster processes from the same data
directory.

ps -ef |grep -i postgres|grep postm
postgres  81440  1  0 Jan31 ?00:11:37
/usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
postgres  97072  81440  0 12:17 ?00:00:00
/usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
postgres  97074  81440  0 12:17 ?00:00:00
/usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data

The streaming replication with one standby looks fine.

I was expecting to see only one postmaster process instead of three and the
time shown in PS output for two extra processes changes to current time
with every PS command I enter. Secondly, I logfile is full of "Incomplete
startup packet" message.

I need help from you experts, Is this the right behaviour of postgres? what
could have gone wrong in my case.

Best Regards
Vikas

Re: Multiple postmasters running from same directory

[Laurenz Albe]

Vikas Sharma wrote:
> We are running Postgresql 9.4 with streaming replication and repmgr. 
> Operating system is RHEL6.8
> 
> On the master I can see multiple postmaster processes from the same data 
> directory. 
> 
> ps -ef |grep -i postgres|grep postm
> postgres  81440  1  0 Jan31 ?00:11:37 
> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
> postgres  97072  81440  0 12:17 ?00:00:00 
> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
> postgres  97074  81440  0 12:17 ?00:00:00 
> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
> 
> The streaming replication with one standby looks fine.
> 
> I was expecting to see only one postmaster process instead of three and the 
> time shown in
> PS output for two extra processes changes to current time with every PS 
> command I enter.
> Secondly, I logfile is full of "Incomplete startup packet" message.
> 
> I need help from you experts, Is this the right behaviour of postgres? what 
> could have gone wrong in my case.

That looks ok.

The two other processes are children of the postmaster.
It is strange that their process title did not get updated.

What do you see for the processes with "pid" 97072 and 97074 in 
pg_stat_activity?

The "incomplete startup packet" is caused by processes that connect to the
PostgreSQL TCP port, but don't complete a database connection.
Often these are monitoring or load balancing programs.

Yours,
Laurenz Albe

Re: Require assistance in Postgres + Hibernate : Error: java.math.BigInteger cannot be cast to java.math.BigDecimal

[Francisco Olarte]

On Tue, Feb 13, 2018 at 12:39 PM, rob stone  wrote:
>> long count = ((BigDecimal)ic.get(“EB”)).longValue();
..
> The method is documented as:-
> public static BigDecimal valueOf(long val)

His problem seems to be the opposit, he does not have the long value
and wants it.

Francisco Olarte.

Require assistance in Postgres + Hibernate : Error: java.math.BigInteger cannot be cast to java.math.BigDecimal

[Vinodh NV]

Hi,

Require assistance on the below:

Code snippet:
Map ic;
//Populate values for ic
long count = ((BigDecimal)ic.get(“EB”)).longValue();


Getting the below error:
java.math.BigInteger cannot be cast to java.math.BigDecimal
®java.lang.ClassCastException: java.math.BigInteger cannot be cast to
java.math.BigDecimal

Since this is not part of a query doing select cast (EB as bigint) will not
help in this case:

Can u let me know the solution for this?

Regards,
Vinodh

Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.

[Robert Zenz]

We are seeing a quite heavy slow down when using prepared statements in 10.1.

I haven't done some thorough testing, to be honest, but what we are having is a
select from a view (complexity of it should not matter in my opinion), something
like this:

prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and
COLUMNB = $2;

-- Actual test code follows.

-- Takes ~2 seconds.
select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35;

-- Takes ~10 seconds.
execute TEST ('N', 35);

Both return the same amount of rows, order of execution does not matter, these
times are reproducible. If the same select statement is executed through JDBC it
takes roughly 6 seconds (execution time only, no data fetched at that point).
I'm a little bit at a loss here. Is such a slow down "expected", did we simply
miss that prepared statements are slower? Or is there something else going on
that we are simply not aware of?

Re: Trying to the behavior of a parallel query with with a change in the transaction isolation mode

[Luis Carril]

Thanks for the answer it worked, the third analyze in the sequence below show 
multiple workers planned and none launched.


PREPARE st AS SELECT avg(a) FROM parallel_big;
EXPLAIN ANALYZE EXECUTE st;

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
EXPLAIN ANALYZE EXECUTE st;
COMMIT;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
EXPLAIN ANALYZE EXECUTE st;
COMMIT;
DEALLOCATE st;

Cheers,
Luis M




From: Alvaro Herrera 
Sent: Monday, February 12, 2018 4:19:52 PM
To: Luis Carril
Cc: pgsql-gene...@postgresql.org
Subject: Re: Trying to the behavior of a parallel query with with a change in 
the transaction isolation mode

Luis Carril wrote:

> The transaction isolation level is serializable. This situation does not 
> normally arise, because parallel query plans are not generated when the 
> transaction isolation level is serializable. However, it can happen if the 
> transaction isolation level is changed to serializable after the plan is 
> generated and before it is executed.

> BEGIN;
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
> EXPLAIN (COSTS OFF) SELECT avg(a) FROM parallel_big;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
> EXPLAIN ANALYZE SELECT avg(a) FROM parallel_big;
> COMMIT;
>
>
>  But complains that after the first SELECT (even if it is in an EXPLAIN) 
> the isolation level cannot be changed, so the transaction is aborted  and the 
> SELECT is never executed (even sequentially).
>
>
>  Is there any way to test the possible behavior described in the 
> documentation?

I think you would do a PREPARE in a regular transaction, then open a
transaction changing the isolation level to serializable and try the
EXPLAIN EXECUTE there.

--
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: oracle_fdw Question

[Laurenz Albe]

chiru r wrote:
> I am trying to install and configure the oracle_fdw on PostgreSQL DB servers.
> What we are trying is, inorder to install (make, make install) oracle_fdw on 
> all DB servers,we want to compile on one server and want build a package/tar 
> file to copy the library files and required SQL scripts into all other 
> servers.
> So that it will reduce the installation efforts.
> 
> can we copy the oracle_fdw.so file into "/9.5/lib/postgresql"
> And  oracle_fdw.control,oracle_fdw--1.1.sql and oracle_fdw--1.0--1.1.sql 
> files into "/9.5/share/postgresql/extension" directory.
> Is this the right approach?
> 
> Is there any process to create re-locatable binary files for oracle_fdw?

As long as you have the same version of the same operating system on
all machines and all required software is installed in the same versions,
it should be no problem to build it on one machine and install it on the others.

The process you describe looks right; just make sure the files end up in the
same directories where "make install" installs them on the build machine.

I am not quite sure what you mean with a "relocatable binary file".

Yours,
Laurenz Albe

RE: Barman 2.3 errors

[GALLIANO Nicolas]

Hi

1/ thanks for your answer
2/ it’ right … the barman support list is better … sorry
3/ i’ve verified the pg replication slot :

postgres@postgres# SELECT slot_name, active, restart_lsn FROM 
pg_replication_slots WHERE slot_type = 'physical' AND slot_name = 'barman';
slot_name | active | restart_lsn
---++-
barman| t  | 1/7B000840


4/ i’ve tried this :


-  Receive-wal process not running :
barman@tcgipocgresql:/var/log/barman [2018/02/13-09:23:53]
$ps -ef |grep receive-wal
barman   48067  4398  0 09:58 pts/000:00:00 grep --color=auto receive-wal


-  Receive-wal process failed start :
$barman receive-wal tcgepg96ddm
Starting receive-wal for server tcgepg96ddm
ERROR: ArchiverFailure:replication slot 'barman' is already in use


-  Receive-wal reset :
$barman receive-wal --reset tcgepg96ddm
Starting receive-wal for server tcgepg96ddm
Resetting receive-wal directory status
Removing status file 
/var/lib/barman/tcgepg96ddm/streaming/00010001007B.partial


-  Switch-xlog :
$barman switch-xlog tcgepg96ddm
The WAL file 00010001007B has been closed on server 'tcgepg96ddm'

Then if i try when the replication slot is not active the receive-wal crash and 
i have this error :

$barman receive-wal tcgepg96ddm
Starting receive-wal for server tcgepg96ddm
EXCEPTION: 'utf8' codec can't decode byte 0xe0 in position 47: invalid 
continuation byte
See log file for more details.

And in the log i still have the UnicodeDecodeError …

On a post (https://sourceforge.net/p/pgbarman/tickets/88/)  i’ve found that 
it’s a barman bug with a workaround … but the solution don’t work for me ...
I’m still looking for a solution ☹

Thanks for your help
Have a good day
nicolas





De : Ahmed, Nawaz [mailto:na...@fast.au.fujitsu.com]
Envoyé : mardi 13 février 2018 08:07
À : GALLIANO Nicolas ; 
pgsql-gene...@postgresql.org
Objet : RE: Barman 2.3 errors


Hi Nicolas,

I would like to take stab at this one, as i had recently worked on a demo of 
barman. But like Michael Paquier said, it is better to check the information i 
provide and the situation you are facing with the maintainers of the project. 
Here is what i found.

The first thing is to check if you can see the replication slot named "barman" 
created on the target database using the below command. It should return a slot 
named "barman" with the slot_type as "physical". The "replication slot: OK" 
line of the check command shows the slot is available, however, please double 
check if it is true with the below query.


select * from pg_replication_slots;


Now let us target the line "WAL archive: FAILED", I faced this issue when i had 
killed the "receive-wal" process and restarted it.  First look for the 
receive-wal process with the ps command

ps -ef|grep receive-wal

if it is not running then start it up in the background using the command

$ barman receive-wal tcgepg96ddm &

If the receive-wal process is running but you still face that error in the 
check command, then I suggest you switch the xlog using the below command.

$ barman switch-xlog tcgepg96ddm

if the above command fails to switch the xlog, then try to force it with the 
below command.

$ barman switch-xlog --force tcgepg96ddm

if you still cannot get it to work, then try to reset the status of the 
receive-wal process using the --reset option as below.

$ barman receive-wal --reset tcgepg96ddm


If you can successfully run the above reset command, then try to switch the log 
file  and run the check command again to see if everything looks fine.

$ barman switch-xlog tcgepg96ddm


Hope that helps, again, these are the steps i had taken to resolve a similar 
issue. You might still want to get in touch with the project maintainers about 
the validity of the above commands.


Best Regards,

Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd
14 Rodborough Road, Frenchs Forest NSW 2086, Australia
T +61 2 9452 9027
na...@fast.au.fujitsu.com
fastware.com.au

[cid:image001.jpg@01D3A4AC.84E4E580]
[cid:image002.jpg@01D3A4AC.84E4E580]

From: GALLIANO Nicolas [mailto:nicolas.galli...@dsi.cnrs.fr]
Sent: Tuesday, 13 February 2018 2:23 AM
To: pgsql-gene...@postgresql.org
Subject: Barman 2.3 errors

Hi,

I’m trying to backup a remote DB (9.6.6) using barman 2.3 but backup failed 
start.
In barman.log i’ve such errors :

2018-02-12 16:18:23,852 [57691] barman.server ERROR: Check 'replication slot' 
failed for server 'tcgepg96ddm'
2018-02-12 16:18:23,857 [57691] barman.server ERROR: Check 'receive-wal 
running' failed for server 'tcgepg96ddm'
2018-02-12 16:18:30,815 [57779] barman.wal_archiver INFO: No xlog segments 
found from streaming for tcgepg96ddm.
2018-02-12 16:18:30,824 [57781] barman.server INFO: Starting receive-wal for 
server tcgepg96ddm
2018-02-12 16:18:30,905 

Re: Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.

[Laurenz Albe]

Robert Zenz wrote:
> We are seeing a quite heavy slow down when using prepared statements in 10.1.
> 
> I haven't done some thorough testing, to be honest, but what we are having is 
> a
> select from a view (complexity of it should not matter in my opinion), 
> something
> like this:
> 
> prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and
> COLUMNB = $2;
> 
> -- Actual test code follows.
> 
> -- Takes ~2 seconds.
> select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35;
> 
> -- Takes ~10 seconds.
> execute TEST ('N', 35);
> 
> Both return the same amount of rows, order of execution does not matter, these
> times are reproducible. If the same select statement is executed through JDBC 
> it
> takes roughly 6 seconds (execution time only, no data fetched at that point).
> I'm a little bit at a loss here. Is such a slow down "expected", did we simply
> miss that prepared statements are slower? Or is there something else going on
> that we are simply not aware of?

Most likely, you are seeing the effects of a generic plan being used.

During the first five executions, the prepared statement will run a
"custom plan" generated with the actual parameters.  If the cost estimate
of these plans is not cheaper than the cost estimate of the generic plan
(without substituting the actual parameters), the generic plan will be
used from the sixth execution on.

You can compare the execution plans generated with

  EXPLAIN (ANALYZE, BUFFERS) EXECUTE test ('N', 35);

and

  EXPLAIN (ANALYZE, BUFFERS) select * from OUR_VIEW where COLUMNA = 'N' and 
COLUMNB = 35;

Yours,
Laurenz Albe

Re: session_replication_role meaning?

[Luca Ferrari]

On Wed, Jan 31, 2018 at 5:19 AM, Peter Eisentraut
 wrote:
> The documentation was recently updated in the master branch, so maybe
> you will find this explanation a bit more detailed:
> https://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE
>

Looks a lot more clear to me.


> The global setting for an instance is not affected by whether you are
> replicating.  The replication system is supposed to set the parameter
> when it is applying changes, e.g.,
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/replication/logical/worker.c;h=eedc3a8816bc2f157e62a664bcc57b5f87530be9;hb=958fe549884928cd3bdf009993e9a05df5fd6cee#l1521

Yes, it is clearer from the documentation and the code what the purpose is.

Thanks,
Luca

Re: Multiple postmasters running from same directory

[Tom Lane]

Laurenz Albe  writes:
> Vikas Sharma wrote:
>> On the master I can see multiple postmaster processes from the same data 
>> directory. 
>> ps -ef |grep -i postgres|grep postm
>> postgres  81440  1  0 Jan31 ?00:11:37 
>> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
>> postgres  97072  81440  0 12:17 ?00:00:00 
>> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
>> postgres  97074  81440  0 12:17 ?00:00:00 
>> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data

> The two other processes are children of the postmaster.
> It is strange that their process title did not get updated.

Seeing that they're showing zero runtime, I bet that these are just-forked
children that have not had time to change their process title yet.
The thing that is strange is that you have a steady enough flow of new
connections that there are usually some children like that.

> The "incomplete startup packet" is caused by processes that connect to the
> PostgreSQL TCP port, but don't complete a database connection.
> Often these are monitoring or load balancing programs.

Putting two and two together, you have some monitoring program that is
hitting the postmaster with a constant stream of TCP connection requests
none of which get completed, resulting in a whole lot of useless fork
activity.  Dial down the monitoring.

regards, tom lane

Re: Require assistance in Postgres + Hibernate : Error: java.math.BigInteger cannot be cast to java.math.BigDecimal

[Francisco Olarte]

Vinodh:

You are BOTTOM QUOTING a message which has nothing to thank me from, I
was just trying to clarify a misunderstanding of your original
question.

On Tue, Feb 13, 2018 at 4:14 PM, Vinodh NV  wrote:
> Thanks for the update Francisco. I had one more query:
> In Oracle the syntax for calling a stored procedure is
>Session sess = (Session) entityManager.getDelegate();
> sess.createSQLQuery("{ call reset() }").executeUpdate();
>
> Can you please let me know if the below equivalent for postgres is correct?
>Session sess = (Session) entityManager.getDelegate();
> sess.createSQLQuery("select reset()");

Had you quoted the correct message you would have seen I told you I do
not know hibernate, so no idea whar a session, entityManaer or or
delegates are. I also haven't done anthing serious with Oracle in this
century. So, I cannot tell you much.

Which I do know is in classic postgres you normally call functions
this way, by selecting.

Francisco Olarte.

Re: Require assistance in Postgres + Hibernate : Error: java.math.BigInteger cannot be cast to java.math.BigDecimal

[Vinodh NV]

Thanks for the update Francisco. I had one more query:
In Oracle the syntax for calling a stored procedure is
   Session sess = (Session) entityManager.getDelegate();
sess.createSQLQuery("{ call reset() }").executeUpdate();

Can you please let me know if the below equivalent for postgres is correct?
   Session sess = (Session) entityManager.getDelegate();
sess.createSQLQuery("select reset()");

Regards,
Vinodh



On Tue, Feb 13, 2018 at 5:12 PM, Francisco Olarte 
wrote:

> On Tue, Feb 13, 2018 at 12:39 PM, rob stone  wrote:
> >> long count = ((BigDecimal)ic.get(“EB”)).longValue();
> ..
> > The method is documented as:-
> > public static BigDecimal valueOf(long val)
>
> His problem seems to be the opposit, he does not have the long value
> and wants it.
>
> Francisco Olarte.
>

cursors and function question

[armand pirvu]

Hi 

Is there any elegant way not a two steps way I can output the cursor value at 
each step?


testtbl table has this content

col1|col2| col3 
++--
 E1 | CAT1   |0
 E1 | CAT2   |0
 E1 | CAT3   |0
 E4 | CAT1   |0
 E5 | CAT1   |0
 E6 | CAT1   |0
 E7 | CAT1   |0


This works
BEGIN WORK;
DECLARE fooc  CURSOR FOR SELECT * FROM testtbl;
FETCH ALL FROM fooc;
CLOSE fooc;
COMMIT WORK;

col1|col2| col3 
++--
 E1 | CAT1   |0
 E1 | CAT2   |0
 E1 | CAT3   |0
 E4 | CAT1   |0
 E5 | CAT1   |0
 E6 | CAT1   |0
 E7 | CAT1   |0


But 
CREATE OR REPLACE FUNCTION foofunc()
   RETURNS text AS $$
DECLARE 
 var2   RECORD;
 cur CURSOR FOR SELECT * from testtbl;
BEGIN
   OPEN cur;
LOOP
  FETCH cur INTO var2;
  return var2;
   END LOOP;
   CLOSE cur;
END; $$
LANGUAGE plpgsql;


select foofunc();
foofunc
---
 ("E1","CAT1  ",0)

But I am looking to get 

foofunc
---
 ("E1","CAT1  ",0)
 ("E1","CATs  ",0)
etc 



Many thanks
— Armand

Re: Multiple postmasters running from same directory

[Tom Lane]

Francisco Olarte  writes:
> On Tue, Feb 13, 2018 at 4:50 PM, Tom Lane  wrote:
>> Putting two and two together, you have some monitoring program that is
>> hitting the postmaster with a constant stream of TCP connection requests
>> none of which get completed, resulting in a whole lot of useless fork
>> activity.  Dial down the monitoring.

> Adding the incomplete startup to the mix, it may be a misconfigured
> monitoring program sending just a byte or two, or zero, and then
> waiting for response, which will give ps more time to catch the child
> in that state. Haven't look at the code, but given messages state with
> 1 identifier byte plus a 4 byte length, many of the forms of reading
> that would lead to a big wait for at least 5 bytes, or for the first
> byte.

Hm, yeah.  From memory, the child process will wait a maximum of 60
seconds to receive a startup packet.  If the hypothesized probing program
sends nothing, or just a small number of bytes, and then sits rather than
closing the connection, then this state would easily persist long enough
to be observable in ps.

If you're not sure where these probes are coming from, turning on
log_connections should help: the "connection received" message comes out
before waiting for the startup packet.

regards, tom lane

Re: cursors and function question

[David G. Johnston]

On Tuesday, February 13, 2018, armand pirvu  wrote:

>
> CREATE OR REPLACE FUNCTION foofunc()
>RETURNS text AS $$
>
> select foofunc();
> foofunc
> ---
>  ("E1","CAT1  ",0)
>
> But I am looking to get
>
> foofunc
> ---
>  ("E1","CAT1  ",0)
>  ("E1","CATs  ",0)
>
>
You need to specify SETOF

CREATE FUNCTION foofunc() RETURNS SETOF text AS

David J.

Re: cursors and function question

[Adrian Klaver]

On 02/13/2018 11:17 AM, armand pirvu wrote:


On Feb 13, 2018, at 12:54 PM, Adrian Klaver > wrote:


On 02/13/2018 10:22 AM, armand pirvu wrote:

Hi
Is there any elegant way not a two steps way I can output the cursor 
value at each step?

testtbl table has this content
col1    |    col2    | col3
++--
 E1 | CAT1   |    0
 E1 | CAT2   |    0
 E1 | CAT3   |    0
 E4 | CAT1   |    0
 E5 | CAT1   |    0
 E6 | CAT1   |    0
 E7 | CAT1   |    0
This works
BEGIN WORK;
DECLARE fooc  CURSOR FOR SELECT * FROM testtbl;
FETCH ALL FROM fooc;
CLOSE fooc;
COMMIT WORK;
col1    |    col2    | col3
++--
 E1 | CAT1   |    0
 E1 | CAT2   |    0
 E1 | CAT3   |    0
 E4 | CAT1   |    0
 E5 | CAT1   |    0
 E6 | CAT1   |    0
 E7 | CAT1   |    0
But
CREATE OR REPLACE FUNCTION foofunc()
   RETURNS text AS $$
DECLARE
 var2   RECORD;
 cur CURSOR FOR SELECT * from testtbl;
BEGIN
   OPEN cur;
LOOP
  FETCH cur INTO var2;
  return var2;
   END LOOP;
   CLOSE cur;
END; $$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION public.foofunc()
RETURNS SETOF testtbl
LANGUAGE sql
AS $function$
   SELECT * FROM testtbl;
$function$


test=> select * from foofunc();
col1 | col2 | col3
--+--+--
E1   | CAT1 |    0
E1   | CAT2 |    0
E1   | CAT3 |    0
E4   | CAT1 |    0
E5   | CAT1 |    0
E6   | CAT1 |    0
E7   | CAT1 |    0
(7 rows)



select foofunc();
foofunc
---
 ("E1    ","CAT1  ",0)
But I am looking to get
foofunc
---
 ("E1    ","CAT1  ",0)
 ("E1    ","CATs  ",0)
etc
Many thanks
— Armand



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




Thanks Adrian

That one I figured it out as well. The idea is that said table has some 
records which I need to loop and do some processing using cursors 
similar with


DECLARE
cur CURSOR FOR SELECT *
     FROM testtbl FOR UPDATE;
BEGIN
   FOR row IN cur LOOP
     UPDATE testtbl
     SET col3=1
     WHERE CURRENT OF cur;
   END LOOP;
   return cur;
END

For a row update the goal is to return the cursor value  be it 
before/after the update, hence my question and test


Not following, are you looking to do this in an UPDATE trigger or 
somewhere else?


Another way to ask is why do you want to use a cursor?



I found some code which seems to do what I need but it involves two 
functions

CREATE or replace FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
     OPEN $1 FOR SELECT col FROM test;
     RETURN $1;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;


And this is what beats  me , aka can I put all in one / how ?




Thanks
Armand












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

Re: How do I get rid of database test-aria

[Adrian Klaver]

On 02/13/2018 12:38 PM, Sherman Willden wrote:

development platform: HP Compaq 6710b
postgresql 9.6
Operating System: Ubuntu 17.10

It probably doesn't matter since there is nothing in the database. I 
don't remember  how I created the database. It appears that postgresql 
does not like a dash in the database name. I have not tried to create a 
table under the database. When I try to drop the database I get the 
following..


date_time=# DROP DATABASE test-aria;
ERROR:  syntax error at or near "-"
LINE 1: DROP DATABASE test-aria;


DROP DATABASE "test-aria";

See below for more info:

https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS



Thanks;

Sherman



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

Re: Windows 10 Pro issue

[Adrian Klaver]

On 02/13/2018 12:07 PM, Dale Seaburg wrote:

Server:  Windows 10 Pro system
Postgresql:  8.4.5 installed several months ago and working correctly, 
until recently


Should have mentioned earlier, version 8.4 went EOL about three and half 
years ago:


https://www.postgresql.org/support/versioning/

Probably should look at upgrading to a newer version sooner rather then 
later.





On Feb 9, 2018 about 7 PM pg_log file recorded:

2018-02-09 15:04:23 CST LOG:  unexpected EOF on client connection
2018-02-09 19:12:41 CST LOG:  received fast shutdown request
2018-02-09 19:12:41 CST LOG:  aborting any active transactions
2018-02-09 19:12:41 CST LOG:  autovacuum launcher shutting down
2018-02-09 19:12:41 CST LOG:  shutting down
2018-02-09 19:12:43 CST LOG:  database system is shut down

On Feb 12, 2018 about 2 PM after computer was restarted, pg_log file 
recorded:


2018-02-12 14:17:31 CST LOG:  could not open configuration file 
"C:/WINDOWS/system32/ConfigDir/pg_hba.conf": No such file or directory

2018-02-12 14:17:31 CST FATAL:  could not load pg_hba.conf

The log event on Friday, appears to have recorded a forced shutdown - 
Windows 10 update install, maybe?  No one at business to record what may 
have happened.


On Monday afternoon, postgres service refused to Start as indicated in 
that day's log event.


Any ideas as to what might have happened, or caused this anomaly? It's 
almost as if some Environment Variables were missing, or scrambled.  I 
don't think Postgresql uses the Windows Registry, in place of EV's, does 
it?


Dale Seaburg






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

Re: cursors and function question

[David G. Johnston]

On Tue, Feb 13, 2018 at 12:03 PM, armand pirvu 
wrote:

>
> ERROR:  RETURN cannot have a parameter in function returning set
> LINE 10:  return  var2;
> HINT:  Use RETURN NEXT or RETURN QUERY.
>
>
> and it just sits there
>
> Any hints ?
>
>
https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
​

David J.

Re: pglogical in postgres 9.6

[greigwise]

No need!  I figured it out.

Had to put this "synchronize_data := false" on the create_subscription call. 
Weird that there seem to be redundant parameters for this; one on the
replication set add and one on the create subscription.  Maybe I'm not quite
understanding the usage on those or something.   If anyone knows the
difference, I'd be interested to hear.

Greig



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

Re: Multiple postmasters running from same directory

[Tom Lane]

Vikas Sharma  writes:
> So is it normal for postgres to fork out new postmaster processes from the
> same data directory? I haven't seen this earlier.

They're not postmasters, they're child processes, as you can easily tell
from the PID/PPID columns of your ps output.  But a process inherits its
title from the parent at fork(), and per this discussion, they haven't
changed it yet.

regards, tom lane

Re: pglogical in postgres 9.6

[Adrian Klaver]

On 02/12/2018 06:09 PM, greigwise wrote:

hireology_tmp=# \dx
 List of installed extensions
Name   | Version |  Schema  |
Description
--+-+--+
  pglogical| 2.0.0   | pglogical| PostgreSQL Logical
Replication
  pglogical_origin | 1.0.0   | pglogical_origin | Dummy extension for
compatibility when upgrading from Postgres 9.4


To get an answer you might to file an issue here:

https://github.com/2ndQuadrant/pglogical/issues





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





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

Windows 10 Pro issue

[Dale Seaburg]

Server:  Windows 10 Pro system
Postgresql:  8.4.5 installed several months ago and working correctly, 
until recently


On Feb 9, 2018 about 7 PM pg_log file recorded:

2018-02-09 15:04:23 CST LOG:  unexpected EOF on client connection
2018-02-09 19:12:41 CST LOG:  received fast shutdown request
2018-02-09 19:12:41 CST LOG:  aborting any active transactions
2018-02-09 19:12:41 CST LOG:  autovacuum launcher shutting down
2018-02-09 19:12:41 CST LOG:  shutting down
2018-02-09 19:12:43 CST LOG:  database system is shut down

On Feb 12, 2018 about 2 PM after computer was restarted, pg_log file 
recorded:


2018-02-12 14:17:31 CST LOG:  could not open configuration file 
"C:/WINDOWS/system32/ConfigDir/pg_hba.conf": No such file or directory

2018-02-12 14:17:31 CST FATAL:  could not load pg_hba.conf

The log event on Friday, appears to have recorded a forced shutdown - 
Windows 10 update install, maybe?  No one at business to record what may 
have happened.


On Monday afternoon, postgres service refused to Start as indicated in 
that day's log event.


Any ideas as to what might have happened, or caused this anomaly? It's 
almost as if some Environment Variables were missing, or scrambled.  I 
don't think Postgresql uses the Windows Registry, in place of EV's, does it?


Dale Seaburg

Re: Connection loosing at some places - caused by firewall

[George Neuner]

On Tue, 14 Nov 2017 12:09:31 +0100, Durumdara 
wrote:


>*I disabled my firewall at home - the [keepalive] problem vanished!!!*

What firewall are you using?  Windows own firewall doesn't interfere
with keepalive packets.  Most commercial SOHO firewalls won't either.

George

Re: Multiple postmasters running from same directory

[Vikas Sharma]

Thanks Tom,

So is it normal for postgres to fork out new postmaster processes from the
same data directory? I haven't seen this earlier.

I will check from where those connection requests are coming in,

Best Regards
Vikas

On Feb 13, 2018 15:50, "Tom Lane"  wrote:

> Laurenz Albe  writes:
> > Vikas Sharma wrote:
> >> On the master I can see multiple postmaster processes from the same
> data directory.
> >> ps -ef |grep -i postgres|grep postm
> >> postgres  81440  1  0 Jan31 ?00:11:37
> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
> >> postgres  97072  81440  0 12:17 ?00:00:00
> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
> >> postgres  97074  81440  0 12:17 ?00:00:00
> /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
>
> > The two other processes are children of the postmaster.
> > It is strange that their process title did not get updated.
>
> Seeing that they're showing zero runtime, I bet that these are just-forked
> children that have not had time to change their process title yet.
> The thing that is strange is that you have a steady enough flow of new
> connections that there are usually some children like that.
>
> > The "incomplete startup packet" is caused by processes that connect to
> the
> > PostgreSQL TCP port, but don't complete a database connection.
> > Often these are monitoring or load balancing programs.
>
> Putting two and two together, you have some monitoring program that is
> hitting the postmaster with a constant stream of TCP connection requests
> none of which get completed, resulting in a whole lot of useless fork
> activity.  Dial down the monitoring.
>
> regards, tom lane
>

Re: cursors and function question

[Adrian Klaver]

On 02/13/2018 10:22 AM, armand pirvu wrote:

Hi

Is there any elegant way not a two steps way I can output the cursor value at 
each step?


testtbl table has this content

 col1|col2| col3
++--
  E1 | CAT1   |0
  E1 | CAT2   |0
  E1 | CAT3   |0
  E4 | CAT1   |0
  E5 | CAT1   |0
  E6 | CAT1   |0
  E7 | CAT1   |0


This works
BEGIN WORK;
DECLARE fooc  CURSOR FOR SELECT * FROM testtbl;
FETCH ALL FROM fooc;
CLOSE fooc;
COMMIT WORK;

 col1|col2| col3
++--
  E1 | CAT1   |0
  E1 | CAT2   |0
  E1 | CAT3   |0
  E4 | CAT1   |0
  E5 | CAT1   |0
  E6 | CAT1   |0
  E7 | CAT1   |0


But
CREATE OR REPLACE FUNCTION foofunc()
RETURNS text AS $$
DECLARE
  var2   RECORD;
  cur CURSOR FOR SELECT * from testtbl;
BEGIN
OPEN cur;
 LOOP
   FETCH cur INTO var2;
   return var2;
END LOOP;
CLOSE cur;
END; $$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION public.foofunc()
 RETURNS SETOF testtbl
 LANGUAGE sql
AS $function$
SELECT * FROM testtbl;
$function$


test=> select * from foofunc();
 col1 | col2 | col3
--+--+--
 E1   | CAT1 |0
 E1   | CAT2 |0
 E1   | CAT3 |0
 E4   | CAT1 |0
 E5   | CAT1 |0
 E6   | CAT1 |0
 E7   | CAT1 |0
(7 rows)





select foofunc();
 foofunc
---
  ("E1","CAT1  ",0)

But I am looking to get

 foofunc
---
  ("E1","CAT1  ",0)
  ("E1","CATs  ",0)
etc



Many thanks
— Armand




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

Re: cursors and function question

[armand pirvu]

> On Feb 13, 2018, at 12:54 PM, Adrian Klaver  wrote:
> 
> On 02/13/2018 10:22 AM, armand pirvu wrote:
>> Hi
>> Is there any elegant way not a two steps way I can output the cursor value 
>> at each step?
>> testtbl table has this content
>> col1|col2| col3
>> ++--
>>  E1 | CAT1   |0
>>  E1 | CAT2   |0
>>  E1 | CAT3   |0
>>  E4 | CAT1   |0
>>  E5 | CAT1   |0
>>  E6 | CAT1   |0
>>  E7 | CAT1   |0
>> This works
>> BEGIN WORK;
>> DECLARE fooc  CURSOR FOR SELECT * FROM testtbl;
>> FETCH ALL FROM fooc;
>> CLOSE fooc;
>> COMMIT WORK;
>> col1|col2| col3
>> ++--
>>  E1 | CAT1   |0
>>  E1 | CAT2   |0
>>  E1 | CAT3   |0
>>  E4 | CAT1   |0
>>  E5 | CAT1   |0
>>  E6 | CAT1   |0
>>  E7 | CAT1   |0
>> But
>> CREATE OR REPLACE FUNCTION foofunc()
>>RETURNS text AS $$
>> DECLARE
>>  var2   RECORD;
>>  cur CURSOR FOR SELECT * from testtbl;
>> BEGIN
>>OPEN cur;
>> LOOP
>>   FETCH cur INTO var2;
>>   return var2;
>>END LOOP;
>>CLOSE cur;
>> END; $$
>> LANGUAGE plpgsql;
> 
> 
> CREATE OR REPLACE FUNCTION public.foofunc()
> RETURNS SETOF testtbl
> LANGUAGE sql
> AS $function$
>SELECT * FROM testtbl;
> $function$
> 
> 
> test=> select * from foofunc();
> col1 | col2 | col3
> --+--+--
> E1   | CAT1 |0
> E1   | CAT2 |0
> E1   | CAT3 |0
> E4   | CAT1 |0
> E5   | CAT1 |0
> E6   | CAT1 |0
> E7   | CAT1 |0
> (7 rows)
> 
> 
>> select foofunc();
>> foofunc
>> ---
>>  ("E1","CAT1  ",0)
>> But I am looking to get
>> foofunc
>> ---
>>  ("E1","CAT1  ",0)
>>  ("E1","CATs  ",0)
>> etc
>> Many thanks
>> — Armand
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 


Thanks Adrian

That one I figured it out as well. The idea is that said table has some records 
which I need to loop and do some processing using cursors similar with 

DECLARE
cur CURSOR FOR SELECT *
FROM testtbl FOR UPDATE;
BEGIN
  FOR row IN cur LOOP
UPDATE testtbl
SET col3=1
WHERE CURRENT OF cur;
  END LOOP;
  return cur;
END

For a row update the goal is to return the cursor value  be it before/after the 
update, hence my question and test

I found some code which seems to do what I need but it involves two functions
  
CREATE or replace FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;


And this is what beats  me , aka can I put all in one / how ?




Thanks
Armand

How do I get rid of database test-aria

[Sherman Willden]

development platform: HP Compaq 6710b
postgresql 9.6
Operating System: Ubuntu 17.10

It probably doesn't matter since there is nothing in the database. I don't
remember  how I created the database. It appears that postgresql does not
like a dash in the database name. I have not tried to create a table under
the database. When I try to drop the database I get the following..

date_time=# DROP DATABASE test-aria;
ERROR:  syntax error at or near "-"
LINE 1: DROP DATABASE test-aria;

Thanks;

Sherman

Re: How do I get rid of database test-aria

[Charles Clavadetscher]

Hi

> On 13.02.2018, at 21:38, Sherman Willden  wrote:
> 
> development platform: HP Compaq 6710b
> postgresql 9.6
> Operating System: Ubuntu 17.10
> 
> It probably doesn't matter since there is nothing in the database. I don't 
> remember  how I created the database. It appears that postgresql does not 
> like a dash in the database name. I have not tried to create a table under 
> the database. When I try to drop the database I get the following..
> 
> date_time=# DROP DATABASE test-aria;
> ERROR:  syntax error at or near "-"
> LINE 1: DROP DATABASE test-aria;
> 

Does
DROP DATABASE "test-aria";
work?

Regards
Charles

> Thanks;
> 
> Sherman

Re: cursors and function question

[armand pirvu]

> On Feb 13, 2018, at 1:22 PM, Adrian Klaver  wrote:
> 
> On 02/13/2018 11:17 AM, armand pirvu wrote:
>>> On Feb 13, 2018, at 12:54 PM, Adrian Klaver >> >> >> wrote:
>>> 
>>> On 02/13/2018 10:22 AM, armand pirvu wrote:
 Hi
 Is there any elegant way not a two steps way I can output the cursor value 
 at each step?
 testtbl table has this content
 col1|col2| col3
 ++--
  E1 | CAT1   |0
  E1 | CAT2   |0
  E1 | CAT3   |0
  E4 | CAT1   |0
  E5 | CAT1   |0
  E6 | CAT1   |0
  E7 | CAT1   |0
 This works
 BEGIN WORK;
 DECLARE fooc  CURSOR FOR SELECT * FROM testtbl;
 FETCH ALL FROM fooc;
 CLOSE fooc;
 COMMIT WORK;
 col1|col2| col3
 ++--
  E1 | CAT1   |0
  E1 | CAT2   |0
  E1 | CAT3   |0
  E4 | CAT1   |0
  E5 | CAT1   |0
  E6 | CAT1   |0
  E7 | CAT1   |0
 But
 CREATE OR REPLACE FUNCTION foofunc()
RETURNS text AS $$
 DECLARE
  var2   RECORD;
  cur CURSOR FOR SELECT * from testtbl;
 BEGIN
OPEN cur;
 LOOP
   FETCH cur INTO var2;
   return var2;
END LOOP;
CLOSE cur;
 END; $$
 LANGUAGE plpgsql;
>>> 
>>> 
>>> CREATE OR REPLACE FUNCTION public.foofunc()
>>> RETURNS SETOF testtbl
>>> LANGUAGE sql
>>> AS $function$
>>>SELECT * FROM testtbl;
>>> $function$
>>> 
>>> 
>>> test=> select * from foofunc();
>>> col1 | col2 | col3
>>> --+--+--
>>> E1   | CAT1 |0
>>> E1   | CAT2 |0
>>> E1   | CAT3 |0
>>> E4   | CAT1 |0
>>> E5   | CAT1 |0
>>> E6   | CAT1 |0
>>> E7   | CAT1 |0
>>> (7 rows)
>>> 
>>> 
 select foofunc();
 foofunc
 ---
  ("E1","CAT1  ",0)
 But I am looking to get
 foofunc
 ---
  ("E1","CAT1  ",0)
  ("E1","CATs  ",0)
 etc
 Many thanks
 — Armand
>>> 
>>> 
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com  
>>> >
>> Thanks Adrian
>> That one I figured it out as well. The idea is that said table has some 
>> records which I need to loop and do some processing using cursors similar 
>> with
>> DECLARE
>> cur CURSOR FOR SELECT *
>> FROM testtbl FOR UPDATE;
>> BEGIN
>>   FOR row IN cur LOOP
>> UPDATE testtbl
>> SET col3=1
>> WHERE CURRENT OF cur;
>>   END LOOP;
>>   return cur;
>> END
>> For a row update the goal is to return the cursor value  be it before/after 
>> the update, hence my question and test
> 
> Not following, are you looking to do this in an UPDATE trigger or somewhere 
> else?
> 
> Another way to ask is why do you want to use a cursor?
> 
>> I found some code which seems to do what I need but it involves two functions
>> CREATE or replace FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
>> BEGIN
>> OPEN $1 FOR SELECT col FROM test;
>> RETURN $1;
>> END;
>> $$ LANGUAGE plpgsql;
>> BEGIN;
>> SELECT reffunc('funccursor');
>> FETCH ALL IN funccursor;
>> COMMIT;
>> And this is what beats  me , aka can I put all in one / how ?
>> Thanks
>> Armand
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 

Not a trigger , but the idea is we will do some batch processing from said 
table let’s name it testtbl

1 - we get the records using  select for update with a limit 100 for example
2 - update each record using using cursor
3 - print the cursor content so that way I have an idea what was updated

I was thinking that if I can put a unique constraint on the table, I can 
generate a global table in the function , update main table from global table 
and return select from global table

I can see the developer desire to use cursors to minimize some effort on his 
side

Thanks 

Armand

Re: Windows 10 Pro issue

[Cyclix]

I've seen this before with Win10.

1.- Go to Windows ControlPanel-UserAccounts-ManageAnotherAccount, select
postgres and re-enter the Postgres User password, save and close.
2.- Start Windows Services (if running as a service) right click on
Postgres Service-Properties-LogOn then enter the same password click Ok
3.- Restart Postgres Service

With every new update of Win10, it tends to changes some of the folders
permissions, including postgres. Very annoying but common.

Good luck


On 02/14/2018 07:35 AM, Adrian Klaver wrote:
> On 02/13/2018 12:07 PM, Dale Seaburg wrote:
>> Server:  Windows 10 Pro system
>> Postgresql:  8.4.5 installed several months ago and working correctly,
>> until recently
> 
> Should have mentioned earlier, version 8.4 went EOL about three and half
> years ago:
> 
> https://www.postgresql.org/support/versioning/
> 
> Probably should look at upgrading to a newer version sooner rather then
> later.
> 
> 
>>
>> On Feb 9, 2018 about 7 PM pg_log file recorded:
>>
>> 2018-02-09 15:04:23 CST LOG:  unexpected EOF on client connection
>> 2018-02-09 19:12:41 CST LOG:  received fast shutdown request
>> 2018-02-09 19:12:41 CST LOG:  aborting any active transactions
>> 2018-02-09 19:12:41 CST LOG:  autovacuum launcher shutting down
>> 2018-02-09 19:12:41 CST LOG:  shutting down
>> 2018-02-09 19:12:43 CST LOG:  database system is shut down
>>
>> On Feb 12, 2018 about 2 PM after computer was restarted, pg_log file
>> recorded:
>>
>> 2018-02-12 14:17:31 CST LOG:  could not open configuration file
>> "C:/WINDOWS/system32/ConfigDir/pg_hba.conf": No such file or directory
>> 2018-02-12 14:17:31 CST FATAL:  could not load pg_hba.conf
>>
>> The log event on Friday, appears to have recorded a forced shutdown -
>> Windows 10 update install, maybe?  No one at business to record what
>> may have happened.
>>
>> On Monday afternoon, postgres service refused to Start as indicated in
>> that day's log event.
>>
>> Any ideas as to what might have happened, or caused this anomaly? It's
>> almost as if some Environment Variables were missing, or scrambled.  I
>> don't think Postgresql uses the Windows Registry, in place of EV's,
>> does it?
>>
>> Dale Seaburg
>>
>>
>>
> 
> 



signature.asc
Description: OpenPGP digital signature

Re: cursors and function question

[Adrian Klaver]

On 02/13/2018 01:25 PM, armand pirvu wrote:


On Feb 13, 2018, at 1:22 PM, Adrian Klaver > wrote:






Not a trigger , but the idea is we will do some batch processing from 
said table let’s name it testtbl


1 - we get the records using  select for update with a limit 100 for example
2 - update each record using using cursor
3 - print the cursor content so that way I have an idea what was updated

I was thinking that if I can put a unique constraint on the table, I can 
generate a global table in the function , update main table from global 
table and return select from global table


Not entirely sure I know what you are trying to accomplish, still:

1) Not sure you need to use cursor, see here for less complicated way:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

and

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Using RETURN NEXT.

Keeping mind:

https://www.postgresql.org/docs/10/static/plpgsql-cursors.html
"Rather than executing a whole query at once, it is possible to set up a 
cursor that encapsulates the query, and then read the query result a few 
rows at a time. One reason for doing this is to avoid memory overrun 
when the result contains a large number of rows. (However, PL/pgSQL 
users do not normally need to worry about that, since FOR loops 
automatically use a cursor internally to avoid memory problems.) A more 
interesting usage is to return a reference to a cursor that a function 
has created, allowing the caller to read the rows. This provides an 
efficient way to return large row sets from functions."


So if you are keeping the rows to 100 a FOR loop would seem to suffice.

2) By global table do you mean a temporary table? If so not sure that is 
going to work as I am pretty sure it will disappear after the function 
is run. I could see having a permanent table that you INSERT the updated 
rows into with a timestamp. Then you could update the main table from 
that and prune old records using the timestamps.





I can see the developer desire to use cursors to minimize some effort on 
his side


Thanks

Armand





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

PostgreSQL Download

[Thiagarajan Lakshminarayanan]

Hello,

The official postgresql download site (
https://www.postgresql.org/download/linux/redhat/) has a link to download
the tar archives (https://www.enterprisedb.com/download-postgresql-binaries)
or interactive installer from EnterpriseDB.  Is the above download from
EnterpriseDB is open source community software OR is it a paid version?

Please let me know.

Thanks

Re: oracle_fdw Question

[chiru r]

Thank you.

On Tue, Feb 13, 2018 at 3:31 AM, Laurenz Albe 
wrote:

> chiru r wrote:
> > I am trying to install and configure the oracle_fdw on PostgreSQL DB
> servers.
> > What we are trying is, inorder to install (make, make install)
> oracle_fdw on all DB servers,we want to compile on one server and want
> build a package/tar file to copy the library files and required SQL scripts
> into all other servers.
> > So that it will reduce the installation efforts.
> >
> > can we copy the oracle_fdw.so file into "/9.5/lib/postgresql"
> > And  oracle_fdw.control,oracle_fdw--1.1.sql and
> oracle_fdw--1.0--1.1.sql files into 
> "/9.5/share/postgresql/extension"
> directory.
> > Is this the right approach?
> >
> > Is there any process to create re-locatable binary files for oracle_fdw?
>
> As long as you have the same version of the same operating system on
> all machines and all required software is installed in the same versions,
> it should be no problem to build it on one machine and install it on the
> others.
>
> The process you describe looks right; just make sure the files end up in
> the
> same directories where "make install" installs them on the build machine.
>
> I am not quite sure what you mean with a "relocatable binary file".
>
> Yours,
> Laurenz Albe
>

Re: cursors and function question

[David G. Johnston]

On Tue, Feb 13, 2018 at 3:31 PM, Adrian Klaver 
wrote:

> 2) By global table do you mean a temporary table? If so not sure that is
> going to work as I am pretty sure it will disappear after the function is
> run.


​Temporary tables can survive until either session or transaction end -
neither of which occurs automatically when exiting a function.

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

"Temporary tables are automatically dropped at the end of a session, or
optionally at the end of the current transaction (see ON COMMIT below)"

David J.

Re: cursors and function question

[armand pirvu]

> On Feb 13, 2018, at 4:37 PM, David G. Johnston  
> wrote:
> 
> On Tue, Feb 13, 2018 at 3:31 PM, Adrian Klaver  > wrote:
> 2) By global table do you mean a temporary table? If so not sure that is 
> going to work as I am pretty sure it will disappear after the function is run.
> 
> ​Temporary tables can survive until either session or transaction end - 
> neither of which occurs automatically when exiting a function.
> 
> https://www.postgresql.org/docs/10/static/sql-createtable.html 
> 
> 
> "Temporary tables are automatically dropped at the end of a session, or 
> optionally at the end of the current transaction (see ON COMMIT below)"
> 
> David J.
> 

Thank you both of you Adrian and David for the input and help

I owe you some nice red wine :)


Cheers
- Armand

Re: I do not get the point of the information_schema

[Peter J. Holzer]

On 2018-02-12 23:01:41 +0100, Thiemo Kellner wrote:
> I try to implement SCD2 on trigger level and try to generated needed code on
> the fly. Therefore I need to read data about the objects in the database. So
> far so good. I know of the information_schema and the pg_catalog. The
> documentation for the information_schema states that it 'is defined in the
> SQL standard and can therefore be expected to be portable and remain
> stable'. I can think of a sensible meaning of portable. One cannot port it
> to MariaDB, can one?

You don't port the information schema to MariaDB. The information schema
is provided by the database.

The *use* of the information schema is portable between standard-
conforming databases, however.

You can use 

select table_schema, table_name
from information_schema.tables
where table_type='BASE TABLE';

on both PostgreSQL and MariaDB to get a list of tables.

(That said, it looks like both PostgreSQL and MariaDB include additional
columns beyond those mandated by the standard - you can't rely on those,
of course. And some databases like Oracle don't even have an information
schema.)

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: How do I get rid of database test-aria

[Adrian Klaver]

On 02/13/2018 03:08 PM, Sherman Willden wrote:
Yes the double quotes worked. I tried single quotes but since that 
didn't work I just assumed that double quotes would not work either.


The below explains why:

https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS




Thanks all;

Sherman

On Tue, Feb 13, 2018 at 1:41 PM, Adrian Klaver 
> wrote:


On 02/13/2018 12:38 PM, Sherman Willden wrote:

development platform: HP Compaq 6710b
postgresql 9.6
Operating System: Ubuntu 17.10

It probably doesn't matter since there is nothing in the
database. I don't remember  how I created the database. It
appears that postgresql does not like a dash in the database
name. I have not tried to create a table under the database.
When I try to drop the database I get the following..

date_time=# DROP DATABASE test-aria;
ERROR:  syntax error at or near "-"
LINE 1: DROP DATABASE test-aria;


DROP DATABASE "test-aria";

See below for more info:


https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS




Thanks;

Sherman



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: I do not get the point of the information_schema

[Peter J. Holzer]

On 2018-02-13 16:06:43 -0700, David G. Johnston wrote:
> On Tue, Feb 13, 2018 at 3:57 PM, Peter J. Holzer  wrote:
> 
> (That said, it looks like both PostgreSQL and MariaDB include additional
> columns beyond those mandated by the standard - you can't rely on those,
> of course. And some databases like Oracle don't even have an information
> schema.)
> 
> 
> Given the documented charter of information_schema I'd present your conclusion
> and evidence to pgsql-bugs...while I suppose extra columns are not inherently
> harmful at minimum they would need to be documented if kept.

I don't have evidence, as I don't have access to a recent SQL standard.
But I noticed that for example information_schema.tables have only a few
columns in common between PostgreSQL and MariaDB:

hjp=> select * from information_schema.tables where table_type='BASE TABLE' 
limit 1;
─[ RECORD 1 ]┬───
table_catalog│ hjp
table_schema │ public
table_name   │ hjpnet
table_type   │ BASE TABLE
self_referencing_column_name │ (∅)
reference_generation │ (∅)
user_defined_type_catalog│ (∅)
user_defined_type_schema │ (∅)
user_defined_type_name   │ (∅)
is_insertable_into   │ YES
is_typed │ NO
commit_action│ (∅)

MariaDB [simba]> select * from information_schema.tables where table_type='BASE 
TABLE' limit 1\G
*** 1. row ***
  TABLE_CATALOG: def
   TABLE_SCHEMA: simba
 TABLE_NAME: archived_versions
 TABLE_TYPE: BASE TABLE
 ENGINE: MyISAM
VERSION: 10
 ROW_FORMAT: Fixed
 TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 7036874417766399
   INDEX_LENGTH: 1024
  DATA_FREE: 0
 AUTO_INCREMENT: 1
CREATE_TIME: 2008-01-28 01:24:48
UPDATE_TIME: 2008-01-28 01:24:48
 CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
   CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.01 sec)

The first 4 are the same, all others are different. 
It is possible that all the columns that PostgreSQL has are required by
the standard and that MariaDB is non-conforming by omitting them, but at
least some of the names look quite PostgreSQL-specific to me. So my
guess is that the standard only requires the first 4 and the rest are
RDBMS-specific.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: I do not get the point of the information_schema

[David G. Johnston]

On Tue, Feb 13, 2018 at 4:17 PM, Peter J. Holzer  wrote:

>
> It is possible that all the columns that PostgreSQL has are required by
> the standard and that MariaDB is non-conforming by omitting them, but at
> least some of the names look quite PostgreSQL-specific to me. So my
> guess is that the standard only requires the first 4 and the rest are
> RDBMS-specific.


Unless our docs are completely misleading I'd say that PostgreSQL is being
conforming while MariaDB is treating information_schema as their version of
pg_catalog (or at least our system views over top of pg_catalog).

​https://www.postgresql.org/docs/10/static/infoschema-tables.html
​
If 5 and 6 and the last columns were not standard conforming it would seem
pointless to include them since we don't have/implement the features they
cover.

David J.

Re: I do not get the point of the information_schema

[Tom Lane]

"David G. Johnston"  writes:
> Unless our docs are completely misleading I'd say that PostgreSQL is being
> conforming while MariaDB is treating information_schema as their version of
> pg_catalog (or at least our system views over top of pg_catalog).

Our project policy is that information_schema should show exactly the
columns mandated by whichever spec version we consider current.  If you
see some that are not in your copy of the standard, you probably need a
newer copy.  (There are links in our wiki to free draft versions of the
spec, which I think are what most of us rely on in practice.)

MariaDB has a much laxer notion of what compliance to the standard means
here, and AFAIK they feel free to add columns that are not in the
standard.  We do not do that.

regards, tom lane

Re: I do not get the point of the information_schema

[David G. Johnston]

On Tue, Feb 13, 2018 at 3:57 PM, Peter J. Holzer  wrote:

> (That said, it looks like both PostgreSQL and MariaDB include additional
> columns beyond those mandated by the standard - you can't rely on those,
> of course. And some databases like Oracle don't even have an information
> schema.)
>

​Given the documented charter of information_schema I'd present your
conclusion and evidence to pgsql-bugs...while I suppose extra columns are
not inherently harmful at minimum they would need to be documented if kept.

David J.

Table Partitioning: Sequence jump issue 10 in 10 with serial datatype

[DrakoRod]

Hi folks!!
I have a problem with a serial data type and partitioned table, I used rules
to insert in child tables. But the problem is that the some does'nt insert
and the sequence value jump sometimes 3 in 3 or 10 in 10. 

The example is the next: 




I don't understand why sequence jumps in this case 4 in 4. And how to avoid
this? I really can't change the use serial data type and how generate the
id, because I don't developed the app.

If I use triggers this don't happen? 

Thanks!






-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: How do I get rid of database test-aria

[Sherman Willden]

Yes the double quotes worked. I tried single quotes but since that didn't
work I just assumed that double quotes would not work either.

Thanks all;

Sherman

On Tue, Feb 13, 2018 at 1:41 PM, Adrian Klaver 
wrote:

> On 02/13/2018 12:38 PM, Sherman Willden wrote:
>
>> development platform: HP Compaq 6710b
>> postgresql 9.6
>> Operating System: Ubuntu 17.10
>>
>> It probably doesn't matter since there is nothing in the database. I
>> don't remember  how I created the database. It appears that postgresql does
>> not like a dash in the database name. I have not tried to create a table
>> under the database. When I try to drop the database I get the following..
>>
>> date_time=# DROP DATABASE test-aria;
>> ERROR:  syntax error at or near "-"
>> LINE 1: DROP DATABASE test-aria;
>>
>
> DROP DATABASE "test-aria";
>
> See below for more info:
>
> https://www.postgresql.org/docs/10/static/sql-syntax-lexical
> .html#SQL-SYNTAX-IDENTIFIERS
>
>
>> Thanks;
>>
>> Sherman
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Table Partitioning: Sequence jump issue 10 in 10 with serial datatype

[DrakoRod]

Sorry, your right! The example is:

CREATE TABLE customers (
id serial PRIMARY KEY, 
name TEXT,
other_data TEXT
);

CREATE TABLE customers_part1(
 CHECK (id<1) 
)INHERITS (customers);

CREATE TABLE customers_part2(
 CHECK (id>=1 AND id<2) 
)INHERITS (customers);

CREATE OR REPLACE RULE inserts_customer_part1
AS ON INSERT TO customers
WHERE new.id < 1
DO INSTEAD  INSERT INTO customers_part1 SELECT NEW.*;

CREATE OR REPLACE RULE inserts_customer_part2
AS ON INSERT TO customers
WHERE new.id >= 1 AND new.id < 2
DO INSTEAD  INSERT INTO customers_part2 SELECT NEW.*;

INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 


dd=# SELECT * FROM customers; 
 id |  name   | other_data 
+-+
  3 | XXx | YY
  7 | XXx | YY
 11 | XXx | YY
 15 | XXx | YY
 19 | XXx | YY
 23 | XXx | YY
(6 rows)




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

unsubsribe

[Welkin]

unsubsribe

Re: pglogical in postgres 9.6

[Jeremy Finzel]

On Tue, Feb 13, 2018 at 1:46 PM greigwise  wrote:

> No need!  I figured it out.
>
> Had to put this "synchronize_data := false" on the create_subscription
> call.
> Weird that there seem to be redundant parameters for this; one on the
> replication set add and one on the create subscription.  Maybe I'm not
> quite
> understanding the usage on those or something.   If anyone knows the
> difference, I'd be interested to hear.
>
> Greig
>
>
>
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
> Yes. When you add a subscription with sync = true, then all tables in the
replication set(s) will be synced. But suppose you want to add a table
later to the replication set. Sync = true will sync only that one table. So
the latter is more granular.

Jeremy