* Is this a Windows *server* version, or is it one of the newer desktop
OS's like Windows 8? Windows 8 seems to shut down rather fast, its quite
possible its not bothering to wait for services to exit.*
I face this problem in Windows XP and Windows 7 but not all windows XP and
Windows 7.
In Win
Hi all,
Looking for some advice regarding a slow query I have and indexing.
I'm using postgresql 9.1 and this is my table that has around 680 rows:
CREATE TABLE mytable
(
class character varying,
floor character varying,
source_id integer,
the_geom geometry
)
WITH (
OIDS=TRUE
);
Bill, thanks for your reply.
"shared_buffers" is set to "128MB".
Now that you mention config file, the only thing I did change there, and
was suggested to me while I made some on my databases was
"max_locks_per_transaction = 5" (which has default value 1).
After resetting "max_locks_per_
On 07/22/2014 11:59 PM, Kalai R wrote:
* Is this a Windows *server* version, or is it one of the newer desktop
OS's like Windows 8? Windows 8 seems to shut down rather fast, its
quite possible its not bothering to wait for services to exit.*
*
*
I face this problem in Windows XP and Windows 7
On 23 Červenec 2014, 15:56, klo uo wrote:
> Bill, thanks for your reply.
>
> "shared_buffers" is set to "128MB".
>
> Now that you mention config file, the only thing I did change there, and
> was suggested to me while I made some on my databases was
> "max_locks_per_transaction = 5" (which has
I'm experimenting with using foreign data wrappers to get data from one
database to another. Most things work perfectly but I am encountering two
issues with triggers on the foreign tables.
The first one is when a query triggers a trigger on the foreign table the
trigger doesn't have any search_pa
On Wed, 23 Jul 2014 10:45:56 +0100
Rebecca Clarke wrote:
> Hi all,
>
> Looking for some advice regarding a slow query I have and indexing.
>
> I'm using postgresql 9.1 and this is my table that has around 680 rows:
>
> CREATE TABLE mytable
> (
> class character varying,
> floor charact
Eelke Klein wrote:
> I'm experimenting with using foreign data wrappers to get data from one
> database to another. Most
> things work perfectly but I am encountering two issues with triggers on the
> foreign tables.
>
> The first one is when a query triggers a trigger on the foreign table the
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Eelke Klein
Sent: Wednesday, July 23, 2014 10:31 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] postgresql_fdw issues with triggers on the foreign tables
I'm experimenting with using forei
hi all,
Upsert is usually defined [1] in reference to a violating a unique key:
Insert, if unique constraint violation then update; or update, if not found
then insert.
Is this theoretically preferable to just looking for a row that matches
certain criteria, updating it if found or insertin
I already give log entries before the shutdown incorrectly, in the previous
post
http://www.postgresql.org/message-id/cagxuanmc4zwcjqncaqn-qktj5kzf1pevvt9o_9wftet8kr_...@mail.gmail.com
Also I explain the steps to install in the same post
http://www.postgresql.org/message-id/cagxuano8jgxeplpfxg1
On 7/23/2014 10:21 AM, Seamus Abshere wrote:
hi all,
Upsert is usually defined [1] in reference to a violating a unique key:
Insert, if unique constraint violation then update; or update, if not
found then insert.
Is this theoretically preferable to just looking for a row that
matches certa
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Wednesday, July 23, 2014 1:32 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why is unique constraint needed for upsert?
On 7/23/2014 10:21
Hi Anil:
On Tue, Jul 22, 2014 at 6:46 PM, Anil Menon wrote:
> Am a bit confused -which one comes first?
> 1) the 'data'||currval('id01_col1_seq') is parsed first : which means it
>or
> 1) an insert is attempted which causes a sequence.nextval to be performed
...
> I observe the latter on my
John R Pierce writes:
> On 7/23/2014 10:21 AM, Seamus Abshere wrote:
>> Upsert is usually defined [1] in reference to a violating a unique key:
>> Is this theoretically preferable to just looking for a row that
>> matches certain criteria, updating it if found or inserting otherwise?
> what happ
Tomas, thanks for the heads up!
I certainly didn't know what this setting means, except the obvious name.
Your links helped.
I just can't find where was this setting suggested, but IIRC it was in a
guide for migrating OSM to PostGIS, as other tables were just created by
GDAL OGR.
I had this line
I am receiveing dead locks like this:
Jul 22, 2014 11:00:29 PM br.com.fastway.fastdialer.FastDialerDB query
SEVERE: SELECT * FROM ONGOING WHERE STATUS='FILA' ORDER BY TRUNK_ID,
PRIORIDADE_TRONCO, ID;
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 508 waits for Access
Valter Nogueira writes:
> I am receiveing dead locks like this:
> Jul 22, 2014 11:00:29 PM br.com.fastway.fastdialer.FastDialerDB query
> SEVERE: SELECT * FROM ONGOING WHERE STATUS='FILA' ORDER BY TRUNK_ID,
> PRIORIDADE_TRONCO, ID;
> org.postgresql.util.PSQLException: ERROR: deadlock detected
>
On 23 July 2014 08:59, Kalai R wrote:
> * Is this a Windows *server* version, or is it one of the newer desktop
> OS's like Windows 8? Windows 8 seems to shut down rather fast, its quite
> possible its not bothering to wait for services to exit.*
>
> I face this problem in Windows XP and Windo
[ please keep the list cc'd, and avoid top-posting ]
Valter Nogueira writes:
> I get the error in different server with different pg versions.
> In this server PG is:
> PostgreSQL 9.1.13 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro
> 4.6.3-1ubuntu5) 4.6.3, 32-bit
Well, that has the only
Hi,
SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS
t_varchar2_tab)) AS employees
FROM hr.customer
when i run function for table column values to single row function name
is hr.tab_to_largestring
this code from oracle
it return like function collect(character varying)
I don't know what collect actually does, but just guessing, I would say
that you're looking for string_agg()
depesz
On Wed, Jul 23, 2014 at 6:12 PM, Ramesh T
wrote:
> Hi,
>
> SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS
> t_varchar2_tab)) AS employees
> FROM hr.custome
On 07/23/2014 10:30 AM, Kalai R wrote:
I already give log entries before the shutdown incorrectly, in the
previous post
http://www.postgresql.org/message-id/cagxuanmc4zwcjqncaqn-qktj5kzf1pevvt9o_9wftet8kr_...@mail.gmail.com
Also I explain the steps to install in the same post
http://www.postg
On 07/23/2014 09:12 AM, Ramesh T wrote:
Hi,
SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS
t_varchar2_tab)) AS employees
FROM hr.customer
when i run function for table column values to single row function
name is hr.tab_to_largestring
this code from oracle
it return
On 7/23/14 3:40 PM, Tom Lane wrote:
John R Pierce writes:
On 7/23/2014 10:21 AM, Seamus Abshere wrote:
Upsert is usually defined [1] in reference to a violating a unique key:
Is this theoretically preferable to just looking for a row that
matches certain criteria, updating it if found or inser
Seamus Abshere writes:
> On 7/23/14 3:40 PM, Tom Lane wrote:
>> For the OP's benefit --- the subtext John left unstated is that the
>> unique-key mechanism has already solved the problem of preventing
>> concurrent updates from creating duplicate keys.
> What if we treat atomicity as optional?
Y
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional? You could have extremely
readable syntax like:
atomicity is not and never will be optional in PostgreSQL.
-- no guarantees, no index required
UPSERT age = 5 INTO dogs WHERE name = 'Jerry';
and if there's
On 7/23/14 6:03 PM, John R Pierce wrote:
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional?
atomicity is not and never will be optional in PostgreSQL.
I'm wondering what a minimal definition of upsert could be - possibly
separating concurrency handling out a
seamusabshere wrote
>> At READ COMMITTED isolation level, you should always get an atomic insert
>> or update [1]
>
> I just think there are a lot of non-concurrent bulk loading and
> processing workflows that could benefit from the performance advantages
> of upsert (one trip to database).
Bul
seamusabshere wrote
> On 7/23/14 6:03 PM, John R Pierce wrote:
>> On 7/23/2014 1:45 PM, Seamus Abshere wrote:
>>> What if we treat atomicity as optional?
>>
>> atomicity is not and never will be optional in PostgreSQL.
>
> I'm wondering what a minimal definition of upsert could be - possibly
> se
On 7/23/14 6:50 PM, David G Johnston wrote:
seamusabshere wrote
On 7/23/14 6:03 PM, John R Pierce wrote:
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional?
atomicity is not and never will be optional in PostgreSQL.
I'm wondering what a minimal definition of u
On 7/23/2014 3:29 PM, Seamus Abshere wrote:
My argument lives and dies on the assumption that UPSERT would be
useful even if it was (when given with no options) just a macro for
UPDATE db SET b = data WHERE a = key;
IF NOT found THEN
INSERT INTO db(a,b) VALUES (key, data);
END IF;
>
>
> hi David,
>
> My argument lives and dies on the assumption that UPSERT would be useful
> even if it was (when given with no options) just a macro for
>
> > UPDATE db SET b = data WHERE a = key;
> > IF NOT found THEN
> > INSERT INTO db(a,b) VALUES (key, data);
> > END IF;
>
> Adding
On 7/23/14 7:45 PM, John R Pierce wrote:
On 7/23/2014 3:29 PM, Seamus Abshere wrote:
My argument lives and dies on the assumption that UPSERT would be
useful even if it was (when given with no options) just a macro for
UPDATE db SET b = data WHERE a = key;
IF NOT found THEN
INSERT INTO
I have a collection of relationship rows of the form
Table: graph
key1 varchar
key2 varchar
A row of the form ('a','b') indicates that 'a' and 'b' are related.
The table contains many relationships between keys, forming several
disjoint sets. All relationships are bi-directional, and both
On 7/23/2014 3:58 PM, Seamus Abshere wrote:
Right - if you had a situation where that might happen, you would use
a slightly more advanced version of the UPSERT command (and/or add a
unique index).
a unique index wouldn't resolve the problem. without one, you'd end up
with two records, with
Thanks Olarte,
Exactly following your advice...this being the beauty of open source -you
can read the source code
. It's that itch to drink deep from the fountain of knowledge.
I really do like
Laurenz Albe's advice of using WITH() AS which seems to be the best
practice I can ask the devel
Quoting David G Johnston :
Nick Guenther wrote
Dear List,
In principle, I am looking for some way to say
```
CREATE VIEW view13131 AS select (id, name, bank_account) from actors
where age > 22;
WATCH view13131;
```
and get output to stdout like
```
INSERT view13131 VALUES (241, "Morti
I set up the following to log all DDLs executed in the database:
CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
CREATE OR REPLACE FUNCTION log_ddl_execution()
RETURNS event_trigger AS $$
DECLARE
insertquery TEXT;
BEGIN
insertquery := 'INSERT INTO log_ddl_info VALUES
On 07/23/2014 05:22 PM, Vasudevan, Ramya wrote:
I set up the following to log all DDLs executed in the database:
CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
CREATE OR REPLACE FUNCTION log_ddl_execution()
RETURNS event_trigger AS $$
DECLARE
insertquery TEXT;
I wouldn't do this with recursion; plain old iteration is your friend
(yes, WITH RECURSIVE is actually iterative, not recursive...)
The algorithm goes like this:
1. Extend your graph relation to be symmetric and transitive.
2. Assign a integer group id to each node.
3. Repeatedly join the node li
I have a suggestion for a table checksumming facility within PostgreSQL.
The applications are reasonably obvious - detecting changes to tables,
validating data migrations, unit testing etc. A possible algorithm is as
follows:
1. For each row of the table, take the binary representations of the
v
https://gist.github.com/wishdev/635f7a839877d79a6781
Sorry for the 3rd party site - just easier to get the layout correct.
A CTE and dense_rank is all it takes. I am always amazed at what one can
now pack into such small amounts of code.
On Wed, Jul 23, 2014 at 4:00 PM, Jim Garrison wrote:
Hi,
Any assistance in interpreting the logs is much appreciated.
Replication server crashes. Below is snippet from log.
2014-07-22 23:36:23 EDT LOG: started streaming WAL from pr
imary at 12/B000 on timeline 1
2014-07-22 23:43:12 EDT FATAL: could not receive data from
WAL stream: server cl
On Tue, Jul 22, 2014 at 2:45 AM, Guillaume Lelarge
wrote:
> Unfortunately, no. Even with the latest release. pg_stat_activity shows you
> what the client fires, not what the server does.
pg_stat_statements has a "track" GUC which controls whether or not
nested statements, such as statements exec
On Thu, Jul 24, 2014 at 12:53 PM, Fabio Milano wrote:
> Any assistance in interpreting the logs is much appreciated.
> Replication server crashes. Below is snippet from log.
> 2014-07-22 23:36:23 EDT LOG: started streaming WAL from pr
> imary at 12/B000 on timeline 1
> 2014-07-22 23:43:12 EDT
Thank You so much Krystian Bigaj.
Since last 2 years I had suffering this problem. But today I got solution
from you.
I am developing .Net application with Postgres.
I am using WCF service (host as windows service) to connect postgres
database.
*My workaround: own service which will start/stop Po
Nick Guenther wrote
> As you said, attaching the trigger to a view is useless (for
> BEFORE/AFTER, which I'm interested in, also only works on statement
> level changes, which I would rather not have to deal with). I tried
> attaching my trigger to a materialized view and found that postgres
48 matches
Mail list logo
