[GENERAL] Postgresql and GlassFish - cannot commit when autoCommit is enabled

[Александр Свиридов]

I have postgresql 9.4 and glassfish 4.1. Besides I use MyBatis inside EJB. Now 
I try to make  select from table and this is what I get:
javax.resource.spi.LocalTransactionException:Cannot commit when autoCommit is 
enabled.
at 
com.sun.gjc.spi.LocalTransactionImpl.commit(LocalTransactionImpl.java:112)
at 
com.sun.enterprise.resource.ConnectorXAResource.commit(ConnectorXAResource.java:124)
at 
com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:518)
at 
com.sun.enterprise.transaction.JavaEETransactionManagerSimplified.commit(JavaEETransactionManagerSimplified.java:854)
at 
com.sun.ejb.containers.EJBContainerTransactionManager.completeNewTx(EJBContainerTransactionManager.java:719)
at 
com.sun.ejb.containers.EJBContainerTransactionManager.postInvokeTx(EJBContainerTransactionManager.java:503)
at 
com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:4566)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:2074)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:2044)
at 
com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:212)
at 
com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:79)
at com.sun.proxy.$Proxy312.getLsist(UnknownSource)
at sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)
at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at 
com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie.dispatchToMethod(ReflectiveTie.java:143)
at 
com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:173)
at 
com.sun.corba.ee.impl.protocol.ServerRequestDispatcherImpl.dispatchToServant(ServerRequestDispatcherImpl.java:528)
at 
com.sun.corba.ee.impl.protocol.ServerRequestDispatcherImpl.dispatch(ServerRequestDispatcherImpl.java:199)
at 
com.sun.corba.ee.impl.protocol.MessageMediatorImpl.handleRequestRequest(MessageMediatorImpl.java:1549)
at 
com.sun.corba.ee.impl.protocol.MessageMediatorImpl.handleRequest(MessageMediatorImpl.java:1425)
at 
com.sun.corba.ee.impl.protocol.MessageMediatorImpl.handleInput(MessageMediatorImpl.java:930)
at 
com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:213)
at 
com.sun.corba.ee.impl.protocol.MessageMediatorImpl.handleRequest(MessageMediatorImpl.java:694)
at 
com.sun.corba.ee.impl.protocol.MessageMediatorImpl.dispatch(MessageMediatorImpl.java:496)
at 
com.sun.corba.ee.impl.transport.ConnectionImpl$1.dispatch(ConnectionImpl.java:195)
at 
com.sun.corba.ee.impl.transport.ConnectionImpl.read(ConnectionImpl.java:328)
at 
com.sun.corba.ee.impl.transport.ReaderThreadImpl.doWork(ReaderThreadImpl.java:112)
at 
com.sun.corba.ee.impl.threadpool.ThreadPoolImpl$WorkerThread.performWork(ThreadPoolImpl.java:497)
at 
com.sun.corba.ee.impl.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:540)Causedby:
 org.postgresql.util.PSQLException:Cannot commit when autoCommit is enabled.
at 
org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:811)
at 
com.sun.gjc.spi.LocalTransactionImpl.commit(LocalTransactionImpl.java:106)...30 
more
I even tried the following code:
SqlSession session 
=ConnectionFactory.getSession().openSession(false);//AUTOCOMMITList list=null;
try{
session.getConnection().setAutoCommit(false);
TempMapper mapper =(TempMapper)session.getMapper(TempMapper.class);
list=mapper.readAll();
}catch(SQLException 
ex){Logger.getLogger(TempBean.class.getName()).log(Level.SEVERE,null, ex);}
finally{
session.close();}
However the result is the same. When I used gf+mysql+mybatis I changed 
relaxautocommit but for postgresql driver there is no such attribute. How to 
solve it?

Re: [GENERAL] detached query?

[Tim Clarke]

We do this

nohup psql -U (username) -W -f (sqlquery script) (database)  &&

on our linux boxes all the time to run a long query or sequence of them.
If its a scheduled job it just goes into cron and you don't need the
"nohup" or trailing "&&". Let me know if you'd like me to break all that
apart for you.

Tim Clarke

On 07/05/15 02:15, Melvin Davidson wrote:
> Although not a PostgreSQL utility, in Linux, you can use screen or
> tmux to establish a connection and then disconnect.
>
> On Wed, May 6, 2015 at 6:48 PM, David G. Johnston
> mailto:david.g.johns...@gmail.com>> wrote:
>
> On Wed, May 6, 2015 at 3:37 PM, Yves Dorfsman  >wrote:
>
>
> On 9.3, is there any way to start a query, detach from the
> server and have the
> query keep going (long query that updates tables, but nothing
> is returned)?
>
>
> ​ No.  Sessions require an external client to maintain its connection.
>
> David J.
> ​
>  
>
>
>
>
> -- 
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.

[GENERAL] count distinct and group by

[Szymon Guz]

Hi,
I'm not sure why there is a reason for such behaviour.

For this table:

create table bg(id serial primary key, t text);

This works:

select count(id) from bg;

This works:

select count(distinct id) from bg;

And this doesn't:

select count(distinct id) from bg order by id;
ERROR:  column "bg.id" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select count(distinct id) from bg order by id;

thanks,
Szymon

Re: [GENERAL] count distinct and group by

[Magnus Hagander]

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz  wrote:

> Hi,
> I'm not sure why there is a reason for such behaviour.
>
> For this table:
>
> create table bg(id serial primary key, t text);
>
> This works:
>
> select count(id) from bg;
>
> This works:
>
> select count(distinct id) from bg;
>
> And this doesn't:
>
> select count(distinct id) from bg order by id;
> ERROR:  column "bg.id" must appear in the GROUP BY clause or be used in
> an aggregate function
> LINE 1: select count(distinct id) from bg order by id;
>
>
There is no "id" column in the returned dataset to order by. You are just
returning one value, how would it be ordered? (and that row has a column
named "count" - but you can alias it to SELECT count(distinct id) AS id
FROM bg ORDER BY id - it just makes no sense to order a single row..

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] count distinct and group by

[Geoff Winkless]

On 7 May 2015 at 11:23, Szymon Guz  wrote:

> Hi,
> I'm not sure why there is a reason for such behaviour.
>
> select count(distinct id) from bg order by id;
> ERROR:  column "bg.id" must appear in the GROUP BY clause or be used in
> an aggregate function
> LINE 1: select count(distinct id) from bg order by id;
> ​​
>
>
Quite apart from the fact that you're trying to ORDER a recordset that
contains a single row (why?), in Postgres (unlike MySQL) you can't order a
list of values by a column you haven't selected.​

Is this what you're trying to achieve:

SELECT COUNT(*), id FROM bg GROUP BY id ORDER BY id;​

​?​

Geoff

Re: [GENERAL] count distinct and group by

[Andomar]

And this doesn't:

select count(distinct id) from bg order by id;
ERROR:  column "bg.id " must appear in the GROUP BY clause
or be used in an aggregate function
LINE 1: select count(distinct id) from bg order by id;



Your result set will contain one row with the count of distinct ids. 
You can't really order 1 row.


The error message occurs because your result set has one unnamed column: 
count(distinct id).  You could write the query like:


select count(distinct id) as cnt from bg order by cnt;

That would be correct SQL, because the column "cnt" now does exist.   

Kind regards,
Andomar


--
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] count distinct and group by

[Szymon Guz]

On 7 May 2015 at 12:39, Magnus Hagander  wrote:

> On Thu, May 7, 2015 at 12:23 PM, Szymon Guz  wrote:
>
>> Hi,
>> I'm not sure why there is a reason for such behaviour.
>>
>> For this table:
>>
>> create table bg(id serial primary key, t text);
>>
>> This works:
>>
>> select count(id) from bg;
>>
>> This works:
>>
>> select count(distinct id) from bg;
>>
>> And this doesn't:
>>
>> select count(distinct id) from bg order by id;
>> ERROR:  column "bg.id" must appear in the GROUP BY clause or be used in
>> an aggregate function
>> LINE 1: select count(distinct id) from bg order by id;
>>
>>
> There is no "id" column in the returned dataset to order by. You are just
> returning one value, how would it be ordered? (and that row has a column
> named "count" - but you can alias it to SELECT count(distinct id) AS id
> FROM bg ORDER BY id - it just makes no sense to order a single row..
>
>
Oh, right. Thanks. I haven't noticed that there is no id column in the
dataset.

thanks,
Szymon

Re: [GENERAL] count distinct and group by

[Thomas Kellerer]

Geoff Winkless schrieb am 07.05.2015 um 12:39:
> in Postgres (unlike MySQL) you can't order a list of values by a column you 
> haven't selected.​

Of course you can, just not when you are aggregating.




-- 
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] count distinct and group by

[Geoff Winkless]

On 7 May 2015 at 11:54, Thomas Kellerer  wrote:

> Geoff Winkless schrieb am 07.05.2015 um 12:39:
> > in Postgres (unlike MySQL) you can't order a list of values by a column
> you haven't selected.​
>
> Of course you can, just not when you are aggregating.
>
> ​Doh! I missed out that key clause :)

Thanks for correcting me.

Geoff​

Re: [GENERAL] Getting UDR up and running

[cchee-ob]

I used the BDR 0.10.0 Documentation to set up UDR.

Basically you set the UDR node the same way as a BDR node and you use the

bdr.bdr_subscribe function to subscribe to the node that you ran the
brr.bdr_group_create function on.

It works great!

The only caveat is that if you are running any DDL on the UDL node you must
use the bdr.bdr_replicate_ddl_command function to execute the DDL only on
the UDR node.

Carter



--
View this message in context: 
http://postgresql.nabble.com/Getting-UDR-up-and-running-tp5848054p5848331.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Hash function constant in the Hash join algorithm

[Ravi Kiran]

Hi,

As part part of my project, I had to compare the time taken by the Hashjoin
algorithm to that of Nested loop algorithm for the  inner join and Natural
join queries, under two cases. I used six  tables to join each other each
containing 5000 rows each approximately.

The two cases are given below.

1)When the hash function of the of hash join algorithm is not made to
return a constant value, In this case the time taken by the hashjoin
algorithm for the inner join is 1.45 secs and for the natural join is 4.58
secs and the time taken by the nested loop algorithm for inner join is
11.49 secs, and for natural join 46.94 secs.

2) when the hash function of the hash join algorithm is made to return a
 constant value. Here, in the program hashfunc.c, I made all the hash
functions return a constant value. In this case the time taken by the
hashjoin algorithm for inner join is  8.44 secs and for natural join is
29.18 secs, and the time taken by the Nested loop algorithm for inner join
is 11.66 secs and for natural join is 46.9 secs.

According to my understanding when a hashfunction is made to return a
constant, all the tuples are hashed into a single bucket, and therefore the
hashjoin algorithm is converted into a nestedloop algorithm.

>From the above two cases, I am understanding that even even when the hash
function is made to return a constant, The hashjoin agorithm is taking
significantly lower time compared to nested loop.

Could anyone explain why does the hashjoin algorithm takes significantly
lower time compared to nested loop when the hash function is made to return
a constant value or have I done any mistake at any part of the code?

Note - I am using the postgresql9.4 version for coding. I know the post is
long, There might be some grammar mistakes, I regret if  I caused any
convenience to anyone.

Thank you.



-- 
Regards,

K.Ravikiran

ᐧ

Re: [GENERAL] Hash function constant in the Hash join algorithm

[Tom Lane]

Ravi Kiran  writes:
> From the above two cases, I am understanding that even even when the hash
> function is made to return a constant, The hashjoin agorithm is taking
> significantly lower time compared to nested loop.

> Could anyone explain why does the hashjoin algorithm takes significantly
> lower time compared to nested loop when the hash function is made to return
> a constant value or have I done any mistake at any part of the code?

Well, hashjoin would suck the inner relation into memory (into its
hashtable) whereas nestloop would rescan the inner relation each time,
implying more buffer-access overhead, tuple visibility checking, etc.

A closer comparison might be to a nestloop that has a Materialize node
above its inner relation.

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] Any freeware graphic display of DDL software available?

[Rajiv M Ranganath]

If you use Amazon Linux on EC2, we have packaged SchemaSpy support for
it. More details are here,

https://lambda-linux.io/blog/2015/05/07/announcing-schemaspy-support-for-amazon-linux/

Best,
Rajiv

On Thu, Jan 16, 2014 at 7:46 PM, Merlin Moncure 
wrote:
> On Thu, Jan 16, 2014 at 10:45 AM, Susan Cassidy
>  wrote:
>> Is there any free or cheap software that will read in DDL and output
>> a graphic display of it? Preferably showing links for foreign keys.
>>
>> I know about Erwin, but it is too expensive.
>
> I want to give a shout out for schemaspy . I think it's about the best
> ERD tool out there; the graphviz relationship mapping 'just works'
> and you have to spend zero time mucking around with the tool post
> extraction which is a critical flaw with many ERD tools. It's aware
> but there are undocumented switches to remove the ads.
>
> My experience is that ERD tools that require manual steps of any kind
> tend to become quickly out of date and useless.
>
> merlin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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

[GENERAL] Streaming replication and an archivelog

[James Sewell]

Hello All,

I am running 9.4 on Centos.

I have three servers, one master and two slaves. The slaves have the
following recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres host=mastervip port=5432'
restore_command = 'scp -o BatchMode=yes postgres@backuphost:/archived_wals/%f
%p'
recovery_target_timeline= 'latest'

Is there any way to combine following

   - a master switch (ie: if node1 dies and node2 is promoted then node3
   follows node2)
   - using a WAL archive, such that if node2 goes down for two days it will
   get WALs from the archive if they are no longer on the master

At the moment the master switch works fine, but if I was to have a WAL
archive with multiple timelines in it then I would end up in the newest
timeline.

I suppose what I want is the following:

If I am a streaming replica only follow streamed timeline switches, not
archive timeline switches.

Obviously if I am not a streaming replica I need to follow archive timeline
switches so I don't break PIT recovery.

Possible?


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
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: [GENERAL] Streaming replication and an archivelog

[James Sewell]

Hey,

This is an example of what I see when starting up a replica and attaching
it to a master.

In this case the master is in timeline 3, but the archive log goes up to
timeline 6.

2015-05-08 16:23:07 AEST @ ( 0 0)LOG:  database system was interrupted;
last known up at 2015-05-08 16:21:14 AEST
2015-05-08 16:23:07 AEST @ ( 0 0)LOG:  restored log file
"0004.history" from archive
2015-05-08 16:23:08 AEST @ ( 0 0)LOG:  restored log file
"0005.history" from archive
2015-05-08 16:23:08 AEST @ ( 0 0)LOG:  restored log file
"0006.history" from archive
scp: /backup/production/archived_wals/0007.history: No such file or
directory
2015-05-08 16:23:08 AEST @ ( 0 0)LOG:  entering standby mode
2015-05-08 16:23:09 AEST @ ( 0 0)LOG:  restored log file
"0006.history" from archive
scp: /backup/production/archived_wals/0006000B00C2: No such
file or directory
scp: /backup/production/archived_wals/0005000B00C2: No such
file or directory
scp: /backup/production/archived_wals/0004000B00C2: No such
file or directory
2015-05-08 16:23:11 AEST @ ( 0 0)LOG:  restored log file
"0003000B00C2" from archive
2015-05-08 16:23:11 AEST @ ( 0 XX000)FATAL:  requested timeline 6 is not a
child of this server's history
2015-05-08 16:23:11 AEST @ ( 0 XX000)DETAIL:  Latest checkpoint is at
B/C260 on timeline 3, but in the history of the requested timeline, the
server forked off from that timeline at B/BE60.
2015-05-08 16:23:11 AEST @ ( 0 0)LOG:  startup process (PID 21893)
exited with exit code 1
2015-05-08 16:23:11 AEST @ ( 0 0)LOG:  aborting startup due to startup
process failure


Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Fri, May 8, 2015 at 4:28 PM, James Sewell 
wrote:

> Hello All,
>
> I am running 9.4 on Centos.
>
> I have three servers, one master and two slaves. The slaves have the
> following recovery.conf
>
> standby_mode = 'on'
> primary_conninfo = 'user=postgres host=mastervip port=5432'
> restore_command = 'scp -o BatchMode=yes postgres@backuphost:/archived_wals/%f
> %p'
> recovery_target_timeline= 'latest'
>
> Is there any way to combine following
>
>- a master switch (ie: if node1 dies and node2 is promoted then node3
>follows node2)
>- using a WAL archive, such that if node2 goes down for two days it
>will get WALs from the archive if they are no longer on the master
>
> At the moment the master switch works fine, but if I was to have a WAL
> archive with multiple timelines in it then I would end up in the newest
> timeline.
>
> I suppose what I want is the following:
>
> If I am a streaming replica only follow streamed timeline switches, not
> archive timeline switches.
>
> Obviously if I am not a streaming replica I need to follow archive
> timeline switches so I don't break PIT recovery.
>
> Possible?
>
>
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect
> __
>
>
>  Level 2, 50 Queen St, Melbourne VIC 3000
>
> *P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099
>
>

-- 


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