Re: [GENERAL] execute same query only one time?

[David G. Johnston]

On Tue, Feb 9, 2016 at 12:16 PM, Marc Mamin  wrote:

>
>  Hi,
> 
>  is there a best practice to share data between two select statements?
>
> Hi,
> I didn't check the whole thread so forgive me if this was already proposed,
> but maybe you could do something like:
>
> create temp table result2 (...)
>
> query_1:
> WITH cte as (select ..),
> tmp as ( INSERT INTO result2 select ...  from cte),
> SELECT ... from cte;
>
> query_2:
> select * from result2;
>

​It was, more or less.  I'm not sure you buy much using an updating CTE in
lieu of a dedicated statement populating the temporary table.  It seems a
bit more confusing to comprehend and the performance benefit has to be
marginal given we expect to only insert a single row into the temp table.

David J.
​

Re: [GENERAL] execute same query only one time?

[Vitaly Burovoy]

On 2/9/16, Marc Mamin  wrote:
>
> Hi,
>
> is there a best practice to share data between two select statements?
>
> Hi,
> I didn't check the whole thread

Try it[1]. The thread is not so long (21 letters before yours) and it worth it.

> so forgive me if this was already proposed,
> but maybe you could do something like:
>
> create temp table result2 (...)
>
> query_1:
> WITH cte as (select ..),
> tmp as ( INSERT INTO result2 select ...  from cte),
> SELECT ... from cte;
>
> query_2:
> select * from result2;

There is a mistake here: query2 returns the same result as the query_1.

>
> regards,
> Marc Mamin

It is similar to the fourth answer[2] in the thread.

If you are able to create temporary table with all fields of the first
result only for avoiding one statement, it can be rewritten without
CTE and one INNER JOIN (five statements):

BEGIN;

CREATE TEMPORARY TABLE temptable(id ..., col1 ..., col2 ..., ...) ON
COMMIT DROP;

INSERT INTO temptable
SELECT id, col1, col2, ...
FROM t0
WHERE col1 = value1 and col2 = value2 and ...
RETURNING *;

SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id);

COMMIT;

===
If it is hard to detect (or just lazy to write) what types temporary
table has, you can rewrite it as (also five statements):

BEGIN;

CREATE TEMPORARY TABLE temptable ON COMMIT DROP AS
SELECT id, col1, col2, ...
FROM t0
WHERE id = (
  SELECT max(id)
  FROM t0
  WHERE col1 = value1 and col2 = value2 and ...
);

SELECT * FROM temptable;

SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id);

COMMIT;

===
But it is not so useful. In the case in original letter the best way
is to use only two queries and pass id from the result of the first
query as an argument to the second query. See the other letter[3] in
the thread.

Temporary tables are useful for keeping a lot of rows to prevent
copying them between client and server.

[1]http://www.postgresql.org/message-id/flat/56b8e6f9.9070...@posteo.de
[2]http://www.postgresql.org/message-id/cakoswnkrb0kfwhcw9cvocrmks8huzrpvflr0kkcjuyn6juk...@mail.gmail.com
[3]http://www.postgresql.org/message-id/CAKOSWN=wX9Myw4q9mpiqESyizU4tWrGivgbw+3Ef=5q60vi...@mail.gmail.com

-- 
Best regards,
Vitaly Burovoy


-- 
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] no pg_hba.conf entry for replication connection

[Josh berkus]

On 02/09/2016 11:44 AM, Алексей Митропольский wrote:

Hello,

I have tried to set up streaming replication (Postgres 9.4.5.3 Linux).

pg_hba.conf on the master server have entry:

host replication postgres 192.168.50.36 trust


I don't recommend using "trust" for this.  Use a .pgpass file instead to 
save the password.




When I start the standby server (192.168.50.36) I get errors in the logs:

LOG: entering standby mode
LOG: restored log file "...10" from archive
LOG: redo starts at 0/1788
LOG: consistent recovery state reached at 0/1100
LOG: database system is ready to accept read only connections
LOG: scp: /opt/PostgreSQL/9.4/data/arch_dest/..11: No such file or
directory
LOG: invalid magic number  in ljg segment ..11, offset 0
FATAL: could not connect to the primary server: FATAL: no pg_hba.conf
entry for replication connection from host "192.168.50.36",
user "postgres", SSL off


Did you reload the master?


--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


--
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] execute same query only one time?

[Harald Fuchs]

Johannes  writes:

>> What the reason to execute all statements which return different
>> columns at once?
>> 
>>> Saving roundtrips,
>> 
>> In most cases they are not so big. Getting a bunch of duplicated data
>> is wasting you network bandwidth and don't increase speed.
>
> In my and your example no duplicated data (result sets) is send over the
> network. The server do not need to wait until the client snips out the
> id and sends it id in the next query again. So the server can compute
> the result set without external dependencies as fast as possible.

Sounds like what you're really after is a stored procedure, isn't it?



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

[GENERAL] no pg_hba.conf entry for replication connection

[Алексей Митропольский]

Hello,

I have tried to set up streaming replication (Postgres 9.4.5.3 Linux).
pg_hba.conf on the master server have entry:
host replication postgres 192.168.50.36 trust
When I start the standby server (192.168.50.36) I get errors in the logs:
LOG: entering standby mode
LOG: restored log file "...10" from archive
LOG: redo starts at 0/1788
LOG: consistent recovery state reached at 0/1100
LOG: database system is ready to accept read only connections
LOG: scp: /opt/PostgreSQL/9.4/data/arch_dest/..11: No such file or directory
LOG: invalid magic number  in ljg segment ..11, offset 0
FATAL: could not connect to the primary server: FATAL: no pg_hba.conf
entry for replication connection from host "192.168.50.36",
user "postgres", SSL off

Sincerely yours,
Alexey Mitropolsky

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

[David G. Johnston]

On Tue, Feb 9, 2016 at 7:56 AM, Geoff Winkless  wrote:

> On 9 February 2016 at 14:53, Tom Lane  wrote:
> > SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
> >
> > There's no null visible anywhere in that.  I suppose that if there's
> > no row with id=4, there would be a null at runtime,
>
> Well yes, that was the whole point.
>
> > but that's not
> > going to make any difference for parse-time determination of what
> > type the COALESCE() will return.
>
> But when the gwtest subquery _does_ return a value it works, so the
> problem can't be parse-time determination, can it?
>
>
SELECT COALESCE((SELECT 'Yes' FROM (VALUES (1),(2),(3)) tst (id) WHERE
id=2), 'No') AS valid;

​Same error...I tested using the table as well...also the same error for
values of id between 1 and 3.

​9.5.0

David J.
​

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

[Merlin Moncure]

On Tue, Feb 9, 2016 at 5:42 AM, Geoff Winkless  wrote:
> On 8 February 2016 at 16:05, David G. Johnston
>  wrote:
>> While explicit casting of literals can at times be annoying and seemingly
>> unncessary I wouldn't call it unintuitive.
>
> Well that very much depends on your definition of intuitive. If
> something is "seemingly unnecessary" I would say that's the same thing
> as "unintuitive", isn't it?
>
>> Typically, you cannot count on PostgreSQL to cast
>> "unknown" typed data to other types.
>
> I don't believe that I'm suggesting that Postgres should. As far as I
> can see, COALESCE takes values of type anyelement and attempts to
> decide if the types are the same: for example it's unexpectedly quite
> happy to take
>
>  SELECT COALESCE('1', 0);
>
> because (I guess) it takes the "unknown" typed literal '1' and decides
> that it can coerce it into an int; note that it _won't_ do
> COALESCE('1'::text, 0) because that is explicitly typed...
>
> I'm not asking that it coerce an actual value with a genuinely unknown
> type to a text value: I'm simply suggesting that it's unnecessary for
> COALESCE to coerce an unknown-typed NULL into anything (even if you
> ignore that NULL is, as far as I know, equivalent, no matter what its
> type), because as far as COALESCE is concerned the NULL can be
> instantly ignored.

Adding special case behavior to coalesce() is probably not the answer.
coalesce() btw is itself something of a special case due to not being
a proper function. Special cases breed special surprises. I'm too
familiar with the status quo to care much anymore, but if you were to
fix this you'd be wanting to expand the scenarios where 'unknown' can
used.

There are some quirks with the type system but as they say familiarity
can breed contempt.  Be advised of the enormous backwards
compatibility baggage here...history has been fairly unkind to
attempted improvements.  Please don't take that as discouragement --
just setting the stage.

merlin


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

Re: [GENERAL] execute same query only one time?

[David G. Johnston]

On Tuesday, February 9, 2016, Vitaly Burovoy 
wrote:

> On 2/9/16, Harald Fuchs > wrote:
> > Johannes > writes:
> >
> >>> What the reason to execute all statements which return different
> >>> columns at once?
> >>>
>  Saving roundtrips,
> >>>
> >>> In most cases they are not so big. Getting a bunch of duplicated data
> >>> is wasting you network bandwidth and don't increase speed.
> >>
> >> In my and your example no duplicated data (result sets) is send over the
> >> network. The server do not need to wait until the client snips out the
> >> id and sends it id in the next query again. So the server can compute
> >> the result set without external dependencies as fast as possible.
> >
> > Sounds like what you're really after is a stored procedure, isn't it?
>
> Unfortunately, his case is different, because he needs to get two
> different set of rows that is impossible even with stored procedures.
>
>
Correct, though it might be workable to use cursors in this situation.  Not
exactly sure how, though...

David J.

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

[Tom Lane]

Geoff Winkless  writes:
> I'm not asking that it coerce an actual value with a genuinely unknown
> type to a text value: I'm simply suggesting that it's unnecessary for
> COALESCE to coerce an unknown-typed NULL into anything (even if you
> ignore that NULL is, as far as I know, equivalent, no matter what its
> type), because as far as COALESCE is concerned the NULL can be
> instantly ignored.

Leaving aside the question of whether that is actually feasible or
a good idea: how would that improve your original complaint?

SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;

There's no null visible anywhere in that.  I suppose that if there's
no row with id=4, there would be a null at runtime, but that's not
going to make any difference for parse-time determination of what
type the COALESCE() will return.

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] COALESCE requires NULL from scalar subquery has a type

[Geoff Winkless]

On 9 February 2016 at 15:16, David G. Johnston
 wrote:
> Same error...I tested using the table as well...also the same error for
> values of id between 1 and 3.

Oh my.

In my memory, this was working. I try it now, and it doesn't.

Apologies: I've obviously managed to lose track of what worked and what didn't.

Sorry for wasting time.

Geoff


-- 
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] COALESCE requires NULL from scalar subquery has a type

[Geoff Winkless]

On 9 February 2016 at 14:53, Tom Lane  wrote:
> SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
>
> There's no null visible anywhere in that.  I suppose that if there's
> no row with id=4, there would be a null at runtime,

Well yes, that was the whole point.

> but that's not
> going to make any difference for parse-time determination of what
> type the COALESCE() will return.

But when the gwtest subquery _does_ return a value it works, so the
problem can't be parse-time determination, can it?

Geoff


-- 
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] COALESCE requires NULL from scalar subquery has a type

[Geoff Winkless]

On 8 February 2016 at 16:05, David G. Johnston
 wrote:
> While explicit casting of literals can at times be annoying and seemingly
> unncessary I wouldn't call it unintuitive.

Well that very much depends on your definition of intuitive. If
something is "seemingly unnecessary" I would say that's the same thing
as "unintuitive", isn't it?

> Typically, you cannot count on PostgreSQL to cast
> "unknown" typed data to other types.

I don't believe that I'm suggesting that Postgres should. As far as I
can see, COALESCE takes values of type anyelement and attempts to
decide if the types are the same: for example it's unexpectedly quite
happy to take

 SELECT COALESCE('1', 0);

because (I guess) it takes the "unknown" typed literal '1' and decides
that it can coerce it into an int; note that it _won't_ do
COALESCE('1'::text, 0) because that is explicitly typed...

I'm not asking that it coerce an actual value with a genuinely unknown
type to a text value: I'm simply suggesting that it's unnecessary for
COALESCE to coerce an unknown-typed NULL into anything (even if you
ignore that NULL is, as far as I know, equivalent, no matter what its
type), because as far as COALESCE is concerned the NULL can be
instantly ignored.

Geoff


-- 
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] execute same query only one time?

[Vitaly Burovoy]

On 2/9/16, Harald Fuchs  wrote:
> Johannes  writes:
>
>>> What the reason to execute all statements which return different
>>> columns at once?
>>>
 Saving roundtrips,
>>>
>>> In most cases they are not so big. Getting a bunch of duplicated data
>>> is wasting you network bandwidth and don't increase speed.
>>
>> In my and your example no duplicated data (result sets) is send over the
>> network. The server do not need to wait until the client snips out the
>> id and sends it id in the next query again. So the server can compute
>> the result set without external dependencies as fast as possible.
>
> Sounds like what you're really after is a stored procedure, isn't it?

Unfortunately, his case is different, because he needs to get two
different set of rows that is impossible even with stored procedures.

-- 
Best regards,
Vitaly Burovoy


-- 
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] fast refresh materialized view

[Michael Paquier]

On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
 wrote:
>
> Thank you very much. We did n't think about that. We would like to choose
> APACHE LICENSE. We apologize for late reply.

And that would be incompatible with the PostgreSQL license I guess,
per concerns with patents and similar stuff.
-- 
Michael


-- 
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] COALESCE requires NULL from scalar subquery has a type

[Rowan Collins]

David G. Johnston wrote on 08/02/2016 16:05:
On Mon, Feb 8, 2016 at 8:25 AM, Geoff Winkless >wrote:


On 8 February 2016 at 14:49, Tom Lane > wrote:
> Yup.  The output column type of the sub-SELECT is determined without
> reference to its context, so there's nothing causing the
unknown-type
> literal to get assigned a definite type.

Mm. I can follow that, although it makes me unhappy that casting the
literal to a known type fixes this, it seems unintuitive.


​While explicit casting of literals can at times be annoying and 
seemingly unncessary I wouldn't call it unintuitive.


I think if I was designing Postgres's type system (or SQL itself?) from 
scratch, I'd try to make literals look less like strings. I think part 
of what's unintuitive is that we're so used to thinking of 'Yes' as 
representing a text value, when Postgres doesn't see it that way. 
Perhaps if it was "Select text", and even "Select int<42>" it would 
be more obvious that "Select " or "Select <42>" required type 
inference.


But that's just dreaming...

Regards,
--
Rowan Collins
[IMSoP]

Re: [GENERAL] execute same query only one time?

[Vitaly Burovoy]

On 2/9/16, Johannes  wrote:
> Am 09.02.2016 um 00:56 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes  wrote:
>>> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
 On 2/8/16, Johannes  wrote:
> increase speed,

 Speed will be at least the same. In your case either you have to use
 more DDL (like CREATE TEMP TABLE) or get copied columns that leads
 more time to encode/decode and send it via network.
>>>
>>> The time difference is small, yes.
>>> My old variant with executing the first select, remember the returned id
>>> value and paste it into the second query and execute it takes 32ms.
>>>
>>> Your temp table variant need 29ms. Nice to see. That are 10% speed
>>> improvement.
>>
>> I guess you measure it by your app. It is just a measurement error.
>> +-3ms can be a sum of TCP packet loss, system interrupts, system timer
>> inaccuracy, multiple cache missing, different layers (you are using
>> Java, it has a VM and a lot of intermediate abstraction layers).
>
> I know all these facts. I run it a "only" a few times, single threaded.
> With high resolution. The rounded result of 3ms was reliable and thats
> exact enough for my rule of thumb.
> Your temp table variant looks more elegant, and is sightly faster. I'm
> fine with that.
>
>> Remember, my version has 6 statements each of them requires some work
>> at PG's side, plus my version has two joins which usually slower than
>> direct search by a value. Your version has only 4 statements and the
>> only one slow place -- "where" clause in the second select which can
>> be replaced by a value founded in the first select (your version sends
>> more data: value1, value2, ...).
>
> Anyway, it is faster ;)

I can't believe it. I insist it is a measurement error.

>> You also can avoid "begin" and "commit" since default transaction
>> isolation is "READ COMMITTED"[1]:
>>> Also note that two successive SELECT commands can see different data,
>>> even though they are within a single transaction, if other transactions
>>> commit
>>> changes after the first SELECT starts and before the second SELECT
>>> starts.
>
> I know. I did not tell that I run my queries in with repeatable read
> isolation.
> And I read it is wise to bundle multiple queries in an transaction,
> because the overhead of multiple transaction can be avoid to one.

It is not true for transactions with SELECTs only. It is wise to join
multiple queries in one transaction when you do multiple _changes_ in
a DB, because after each change DB must save it into WAL file and
increase shared "Transaction ID sequence".

Also as in your example, transactions with isolation SERIALIZABLE and
REPEATABLE READ are used to do (even RO) queries to the DB in a
consistent state. But RO queries don't become faster.

>> If you want to measure time, run both versions 1 times in 8
>> connections simultaneously and compare results. ;-)
>>
>> 32ms * 10k requests / 8 threads = 4ms = 40sec
>
> Thats more complicated, I think I learned enough about it. But thanks.

Real world is complex. 40 seconds per test is not long.
If you have to save only one param for the other query creating a temp
table is heavy enough.

I'm waiting for a result of 10k requests test.
-- 
Best regards,
Vitaly Burovoy


-- 
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] execute same query only one time?

[Johannes]

Am 09.02.2016 um 00:56 schrieb Vitaly Burovoy:
> On 2/8/16, Johannes  wrote:
>> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
>>> On 2/8/16, Johannes  wrote:
 Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy:
> Hmm. Could you clarify why you don't want to pass id from the first
> query to the second one:
>
> select col1 from t1 where t0_id = value_id_from_the_first_query

 Of course I could do that, but in that case I would not ask.

 I thougt there could be a better solution to execute all statements at
 once.
>>>
>>> What the reason to execute all statements which return different
>>> columns at once?
>>>
 Saving roundtrips,
>>>
>>> In most cases they are not so big. Getting a bunch of duplicated data
>>> is wasting you network bandwidth and don't increase speed.
>>
>> In my and your example no duplicated data (result sets) is send over the
>> network. The server do not need to wait until the client snips out the
>> id and sends it id in the next query again. So the server can compute
>> the result set without external dependencies as fast as possible.
> 
> We are talking about executing all statements at once to save RTT. Are we?

Yes, we do.

> And a parallel thread has advice to join tables (queries). It is a way
> to run both queries at once, but it is not a solution.

Right.

 increase speed,
>>>
>>> Speed will be at least the same. In your case either you have to use
>>> more DDL (like CREATE TEMP TABLE) or get copied columns that leads
>>> more time to encode/decode and send it via network.
>>
>> The time difference is small, yes.
>> My old variant with executing the first select, remember the returned id
>> value and paste it into the second query and execute it takes 32ms.
>>
>> Your temp table variant need 29ms. Nice to see. That are 10% speed
>> improvement.
> 
> I guess you measure it by your app. It is just a measurement error.
> +-3ms can be a sum of TCP packet loss, system interrupts, system timer
> inaccuracy, multiple cache missing, different layers (you are using
> Java, it has a VM and a lot of intermediate abstraction layers).

I know all these facts. I run it a "only" a few times, single threaded.
With high resolution. The rounded result of 3ms was reliable and thats
exact enough for my rule of thumb.
Your temp table variant looks more elegant, and is sightly faster. I'm
fine with that.

> Remember, my version has 6 statements each of them requires some work
> at PG's side, plus my version has two joins which usually slower than
> direct search by a value. Your version has only 4 statements and the
> only one slow place -- "where" clause in the second select which can
> be replaced by a value founded in the first select (your version sends
> more data: value1, value2, ...).

Anyway, it is faster ;)

> You also can avoid "begin" and "commit" since default transaction
> isolation is "READ COMMITTED"[1]:
>> Also note that two successive SELECT commands can see different data,
>> even though they are within a single transaction, if other transactions 
>> commit
>> changes after the first SELECT starts and before the second SELECT starts.

I know. I did not tell that I run my queries in with repeatable read
isolation.
And I read it is wise to bundle multiple queries in an transaction,
because the overhead of multiple transaction can be avoid to one.

> If you want to measure time, run both versions 1 times in 8
> connections simultaneously and compare results. ;-)
> 
> 32ms * 10k requests / 8 threads = 4ms = 40sec

Thats more complicated, I think I learned enough about it. But thanks.

Ciao Johannes



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] execute same query only one time?

[Marc Mamin]

 Hi,

 is there a best practice to share data between two select statements?

Hi,
I didn't check the whole thread so forgive me if this was already proposed,
but maybe you could do something like:

create temp table result2 (...)

query_1:
WITH cte as (select ..),
tmp as ( INSERT INTO result2 select ...  from cte),
SELECT ... from cte;

query_2:
select * from result2;

regards,

Marc Mamin

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

[GENERAL] PostgreSQL vs Firebird SQL

[ioan ghip]

I have a Firebird SQL database running on one of my servers which has about
50k inserts, about 100k updates and about 30k deletes every day. There are
about 4 million records in 24 tables. I have a bunch of stored procedures,
triggers, events and views that I'm using.
Firebird works fairly well, but from time to time the database gets
corrupted and I couldn't figure out yet (after many years of running)
what's the reason. When this happens I run "gfix -mend -full -ignore",
backup and restore the db and everything is fine until next problem in a
week, or a month.

I never used PostgreSQL. Yesterday I installed it on my development machine
and after few tests I saw that it's fairly easy to use.

Does anyone have experience with both, Firebird and PostgreSQL? Is
PostgreSQL way better performing than Firebird? Is it worth the effort
moving away from Firebird? Would I gain stability and increased performance?

Thanks.

[GENERAL] ERROR: missing FROM-clause entry for table

[bigkev]

I am receiving this error for the query pasted below.
Is the LEFT JOIN on the table not enough?
What needs to happen here?
I am guess something to do with derived tables

http://pastie.org/10715876



--
View this message in context: 
http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.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

Re: [GENERAL] PostgreSQL vs Firebird SQL

[Chris Travers]

Hi;

On Wed, Feb 10, 2016 at 5:10 AM, ioan ghip  wrote:

> I have a Firebird SQL database running on one of my servers which has
> about 50k inserts, about 100k updates and about 30k deletes every day.
> There are about 4 million records in 24 tables. I have a bunch of stored
> procedures, triggers, events and views that I'm using.
> Firebird works fairly well, but from time to time the database gets
> corrupted and I couldn't figure out yet (after many years of running)
> what's the reason. When this happens I run "gfix -mend -full -ignore",
> backup and restore the db and everything is fine until next problem in a
> week, or a month.
>

Is this running as an embedded engine or a standalone server?  One thing
about Firebird is that since it is embeddable, in that mode other
application bugs could corrupt the database.  In the other case, I would
expect you may want to run hardware diagnostics to rule out hardware
problems going forward.  If you find hardware problems fix them first, then
look further.

But the low hanging possible things to look at here are moving from an
embedded mode to a standalone server if applicable, and checking out your
hardware.  If these turn out not to be the problem, then I would recommend
moving.


> I never used PostgreSQL. Yesterday I installed it on my development
> machine and after few tests I saw that it's fairly easy to use.
>
> Does anyone have experience with both, Firebird and PostgreSQL? Is
> PostgreSQL way better performing than Firebird? Is it worth the effort
> moving away from Firebird? Would I gain stability and increased performance?
>

I have never seen database corruption on PostgreSQL that was not a result
of either:

1.  Use cases WAY out of the ordinary (and then only years ago and I
reported a bug on this and it was very quickly fixed)
2.  Hardware problems
3.  Heat management problems (sticking a db server in a hot closet, and
then only indexes were corrupted).

I do think on decent hardware you will have no trouble.  In other words,
outside of horrible abuse, PostgreSQL does very well.

The largest PostgreSQL database I have worked with had hundreds of tables,
some containing over a hundred million rows, and took up 9TB+ of storage.
And it processed millions of inserts, deletes, and updates every day (24x7
scientific computing cluster processing the data in the db).   Granted at
that scale performance requires very good hardware and an attention to
detail but with those it runs fine.

I do have experience on both and am generally happier with PostgreSQL but I
can imagine there are cases where the move may be painful.  Stored
procedures are one (though probably not so bad).  The bigger issue I think
you will run into is case folding.  Firebird follows the SQL standard and
folds to upper case.  The PostgreSQL community really doesn't like this and
folds to lower case.  This can require some changes in application code to
make work properly.

So there are my $0.02


>
> Thanks.
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] PostgreSQL vs Firebird SQL

[David Grelaud]

I confirm what Josh berkus said. The performance will not be a problem as
soon as you manage your database very well (good configuration, good
hardware, good queries, good data organisation...), like a lot of other
competitors I think?

We have never used Firebird but we use intensively PostgreSQL since 2011 on
thirty servers (independently).
All our servers run an application which have 150 tables, more than 500
handmade queries, where more than 30 % are really complex (more than 2000
lines of code), using a lot of features (json, hstore, full text search,
window functions, custom types, custom window functions, all kind of
indexes, recursive queries, stored procedures, triggers, locks, a lot of
CTEs, range types, arrays types...).
One of our server, which runs Ubuntu Server on a virtualized machine (4
cores, 16 Go RAM), has more than 100 millions of rows with more or less the
same inserts, updates and deletes every day as you.
Hopefully, we've never experienced a data corruption until now ("crossed
fingers").

*David Grelaud*

2016-02-10 8:06 GMT+01:00 Josh berkus :

> On 02/10/2016 05:10 AM, ioan ghip wrote:
>
>> I have a Firebird SQL database running on one of my servers which has
>> about 50k inserts, about 100k updates and about 30k deletes every day.
>> There are about 4 million records in 24 tables. I have a bunch of stored
>> procedures, triggers, events and views that I'm using.
>> Firebird works fairly well, but from time to time the database gets
>> corrupted and I couldn't figure out yet (after many years of running)
>> what's the reason. When this happens I run "gfix -mend -full -ignore",
>> backup and restore the db and everything is fine until next problem in a
>> week, or a month.
>>
>> I never used PostgreSQL. Yesterday I installed it on my development
>> machine and after few tests I saw that it's fairly easy to use.
>>
>> Does anyone have experience with both, Firebird and PostgreSQL? Is
>> PostgreSQL way better performing than Firebird? Is it worth the effort
>> moving away from Firebird? Would I gain stability and increased
>> performance?
>>
>
> Well, performance in PostgreSQL is largely dependant on your hardware. The
> numbers you're talking about seem pretty small to me, though; I can do 1000
> inserts per *second* on a medium-sized AWS instance.  So I don't think
> performance will be your main concern.
>
> I'm sorry to hear about your data corruption issues on Firebird.  That's
> dissapointing, especially since Firebird was one of the champion early open
> source databases.  Proof against database corruption is a major part of
> PostgreSQL.  I suggest turning on data checksums when you create your
> database (this is not the default option) just in case the corruption issue
> is actually your hardware.
>
> PostgreSQL is a *server* database, though, so managing it is going to be
> fairly different from Firebird, which is primarily a desktop database. I
> suggest looking into pgAdmin4 to help with that.
>
> --
> --
> Josh Berkus
> Red Hat OSAS
> (any opinions are my own)
>
>
> --
> 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] ERROR: missing FROM-clause entry for table

[Michael Paquier]

On Wed, Feb 10, 2016 at 4:11 PM, bigkev  wrote:
> I am receiving this error for the query pasted below.
> Is the LEFT JOIN on the table not enough?
> What needs to happen here?
> I am guess something to do with derived tables
>
> http://pastie.org/10715876

Please be sure to copy the content of your query directly in the emails
sent here. External websites like the one you have your content on are
short-living things, so the content that you are referring to would get
lost from the Postgres archives once your data is removed there or
considered out-of-sync.

What is the complete error message that you think is a bug?
-- 
Michael

Re: [GENERAL] PostgreSQL vs Firebird SQL

[Josh berkus]

On 02/10/2016 05:10 AM, ioan ghip wrote:

I have a Firebird SQL database running on one of my servers which has
about 50k inserts, about 100k updates and about 30k deletes every day.
There are about 4 million records in 24 tables. I have a bunch of stored
procedures, triggers, events and views that I'm using.
Firebird works fairly well, but from time to time the database gets
corrupted and I couldn't figure out yet (after many years of running)
what's the reason. When this happens I run "gfix -mend -full -ignore",
backup and restore the db and everything is fine until next problem in a
week, or a month.

I never used PostgreSQL. Yesterday I installed it on my development
machine and after few tests I saw that it's fairly easy to use.

Does anyone have experience with both, Firebird and PostgreSQL? Is
PostgreSQL way better performing than Firebird? Is it worth the effort
moving away from Firebird? Would I gain stability and increased performance?


Well, performance in PostgreSQL is largely dependant on your hardware. 
The numbers you're talking about seem pretty small to me, though; I can 
do 1000 inserts per *second* on a medium-sized AWS instance.  So I don't 
think performance will be your main concern.


I'm sorry to hear about your data corruption issues on Firebird.  That's 
dissapointing, especially since Firebird was one of the champion early 
open source databases.  Proof against database corruption is a major 
part of PostgreSQL.  I suggest turning on data checksums when you create 
your database (this is not the default option) just in case the 
corruption issue is actually your hardware.


PostgreSQL is a *server* database, though, so managing it is going to be 
fairly different from Firebird, which is primarily a desktop database. 
I suggest looking into pgAdmin4 to help with that.


--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


--
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] ERROR: missing FROM-clause entry for table

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of bigkev
> Sent: Mittwoch, 10. Februar 2016 08:11
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] ERROR: missing FROM-clause entry for table
> 
> I am receiving this error for the query pasted below.
> Is the LEFT JOIN on the table not enough?
> What needs to happen here?
> I am guess something to do with derived tables
> 
> http://pastie.org/10715876

It would help to know for which table the clause entry is missing. I guess that 
the order of the joins is not correct:

left join generate_series(c.start_time, c.end_time, '2 weeks'::interval) 
f(fortnight) ON g.day=f.fortnight
LEFT JOIN call_schedule c on extract(dow from c.start_time) = extract(dow from 
g.day)  AND f.fortnight IS NOT NULL AND g.day BETWEEN
c.start_time AND c.end_time

In the first line you use c, but this is declared on the following line.

Bye
Charles

> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.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



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