I don't like loops to catch "failure" condition... can you possibly fail
to stop?
In a stored procedure (or with auto-commit turned off in any
transaction)... You can avoid any race condition by using a semaphore
(e.g. you lock "something" for the duration of the critical part of your
process
Le 14 janv. 2015 05:22, "Jeremy Palmer" a écrit :
>
> I think PgAdmin is just a client that uses libpq and does not
specifically help with SSO.
>
You're definitely right about that.
>
>
> From: Raghu Ram [mailto:raghuchenn...@gmail.com]
> Sent: Tuesday, 13 January 2015 10:22 p.m.
> To: Jeremy Pa
I think PgAdmin is just a client that uses libpq and does not specifically help
with SSO.
From: Raghu Ram [mailto:raghuchenn...@gmail.com]
Sent: Tuesday, 13 January 2015 10:22 p.m.
To: Jeremy Palmer
Subject: Re: [GENERAL] SSO Windows-to-unix
On Tue, Jan 13, 2015 at 5:15 AM, Jeremy Palmer
mailto
John McKown wrote:
On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
mailto:robert.difa...@gmail.com>>wrote:
Let's say I have two tables like this (I'm leaving stuff out for
simplicity):
CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE hometowns (
id INTE
Hi,
I am quite new to handle a lot of data in Postgres and I would be happy
to get as much advice from experienced data jongleurs as possible.
THE SCENARIO:
I have a big table `client_log`. It is the "main gate" for clients (and
there is a lot of them) to talk to the backend.
Each client sends mo
Good points. I guess my feeling is that if there can be a race condition on
INSERT then the CTE version is not truly atomic, hence the LOOP.
On Tue, Jan 13, 2015 at 3:11 PM, Brian Dunavant wrote:
> A very good point, but it does not apply as here (and in my article)
> we are not using updates, o
A very good point, but it does not apply as here (and in my article)
we are not using updates, only insert and select.
On Tue, Jan 13, 2015 at 6:03 PM, Thomas Kellerer wrote:
> Brian Dunavant wrote on 13.01.2015 22:33:
>>
>> What issue are you having? I'd imagine you have a race condition on
>
Brian Dunavant wrote on 13.01.2015 22:33:
What issue are you having? I'd imagine you have a race condition on
the insert into hometowns, but you'd have that same race condition in
your app code using a more traditional 3 query version as well.
I often use CTEs like this to make things atomic.
The loop to run it twice handles that yes. I don't think that buys
you anything over a more traditional non-cte method though. I'd run
them a few thousand times to see if there's any difference in runtimes
but my guess is the CTE version would be slightly slower here.
v_id integer;
BEGIN;
sele
This seems to get rid of the INSERT race condition.
CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR)
RETURNS INTEGER AS $
DECLARE hometown_id INTEGER;
BEGIN
LOOP
BEGIN
WITH sel AS (
SELECT id FROM hometowns WHERE name = hometown_name
), ins AS (
With the single CTE I don't believe you can do a full upsert loop. If
you're doing this inside of a postgres function, your changes are
already atomic, so I don't believe by switching you are buying
yourself much (if anything) by using a CTE query instead of something
more traditional here.
The a
Well, traditionally I would create a LOOP where I tried the SELECT, if
there was nothing I did the INSERT, if that raised an exception I would
repeat the LOOP.
What's the best way to do it with the CTE? Currently I have the following
which gives me Duplicate Key Exceptions when two sessions try to
What issue are you having? I'd imagine you have a race condition on
the insert into hometowns, but you'd have that same race condition in
your app code using a more traditional 3 query version as well.
I often use CTEs like this to make things atomic. It allows me to
remove transactional code ou
This CTE approach doesn't appear to play well with multiple concurrent
transactions/connections.
On Tue, Jan 13, 2015 at 10:05 AM, John McKown
wrote:
> On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco > wrote:
>
>> Thanks John. I've been seeing a lot of examples like this lately. Does
>> the fo
For what it's worth, this week's run covered even more months than
last week's did, and ran in about 5 1/2 hours, with no slowdowns,
under a similar system load. So, it could have been a one-time thing
or some combination of factors that will be difficult to reproduce.
--
Mike Nolan
--
Sent via
On 01/13/2015 12:11 PM, Vladimir Borodin wrote:
05 янв. 2015 г., в 18:15, Vladimir Borodin написал(а):
Hi all.
I have a simple script for planned switchover of PostgreSQL (9.3 and 9.4)
master to one of its replicas. This script checks a lot of things before doing
it and one of them is that
On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco
wrote:
> Thanks John. I've been seeing a lot of examples like this lately. Does the
> following approach have any advantages over traditional approaches?
>
>
> WITH sel AS (
> SELECT id FROM hometowns WHERE name = 'Portland'
> ), ins AS (
>
Thanks John. I've been seeing a lot of examples like this lately. Does the
following approach have any advantages over traditional approaches?
WITH sel AS (
SELECT id FROM hometowns WHERE name = 'Portland'
), ins AS (
INSERT INTO hometowns(name)
SELECT 'Portland'
WHERE NOT EXISTS (SE
Vladimir Borodin wrote:
> I’m not sure that these 104 bytes will always be 104 bytes to have a
> strict equality while checking. Could it change in the future?
There is no promise that WAL record format stays unchanged. Sometimes
we change a WAL record in a minor release.
> Or is there a better
On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco
wrote:
> Let's say I have two tables like this (I'm leaving stuff out for
> simplicity):
>
> CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
> CREATE TABLE hometowns (
> id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
> name VARCHAR,
>
Let's say I have two tables like this (I'm leaving stuff out for
simplicity):
CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE hometowns (
id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
name VARCHAR,
PRIMARY KEY (id),
UNIQUE(name)
);
CREATE SEQUENCE USER_SEQ_GEN START 1
"Yelai, Ramkumar IN BLR STS" writes:
> I have migrated my postgresql 9.2 schemas to Postgres 9.4 (only table scheme
> migrated not the data ). I have used the following sql to convert table
> output to json in 9.2.
> select array_to_json(array_agg(row_to_json(R.*)))::text from ( select " ID",
On 01/12/2015 10:45 PM, Yelai, Ramkumar IN BLR STS wrote:
Hi
I have migrated my postgresql 9.2 schemas to Postgres 9.4 (only table
scheme migrated not the data ). I have used the following sql to
convert table output to json in 9.2.
select array_to_json(array_agg(row_to_json(R.*)))::text from (
Hi
I have migrated my postgresql 9.2 schemas to Postgres 9.4 (only table scheme
migrated not the data ). I have used the following sql to convert table output
to json in 9.2.
select array_to_json(array_agg(row_to_json(R.*)))::text from ( select " ID", "
TIME" from "SN_TestTable" )R;
IN 9.2,
Many worthwhile things cost money.
I never suggested you wouldn't have to pay.
- Bob
On Tue, Jan 13, 2015 at 12:27 AM, Michael Nolan wrote:
>
>
> On Mon, Jan 12, 2015 at 7:46 PM, Bob Futrelle
> wrote:
>
>> You should be able to find a cloud provider that could give you many TB.
>> Or so they l
05 янв. 2015 г., в 18:15, Vladimir Borodin написал(а):
> Hi all.
>
> I have a simple script for planned switchover of PostgreSQL (9.3 and 9.4)
> master to one of its replicas. This script checks a lot of things before
> doing it and one of them is that all data from master has been received b
Hi Craig, any insight on this issue? :)
Thanks,
Dean
--
View this message in context:
http://postgresql.nabble.com/BDR-Error-restarted-tp5833139p5833722.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgres
27 matches
Mail list logo
