Re: [GENERAL] LEFT JOIN, entry can not be referenced

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson 
> wrote:

>> SELECT
>> wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl.
>> docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue
>> FROM worklogs AS wl, workactions AS wa, users AS u
>> LEFT JOIN documents AS d ON wl.doc_id=d.id
>> LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
>> LEFT JOIN files AS f ON wl.file_id=f.id
>> WHERE wl.action_id=wa.id AND wl.user_id=u.id
>> ORDER BY wl.ts DESC;
>> 
>> When I run this I get the error:
>> 
>> 
>> ERROR:  invalid reference to FROM-clause entry for table "wl"
>> LINE 3: LEFT JOIN documents AS d ON wl.doc_id=d.id
>> ^
>> HINT:  There is an entry for table "wl", but it cannot be referenced
>> from this part of the query.
>> 

> You should write out all of your joins explicitly.
> ...
> Mixing "FROM tbl1, tbl2 WHERE" and "FROM tbl1 JOIN tbl2 ON" syntax just
> causes grief.

More specifically, the commas can be read as CROSS JOINs of the lowest
syntactic priority, so that what you wrote is equivalent to

SELECT ... FROM
  worklogs AS wl
  CROSS JOIN workactions AS wa
  CROSS JOIN (users AS u
  LEFT JOIN documents AS d ON wl.doc_id=d.id
  LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
  LEFT JOIN files AS f ON wl.file_id=f.id)
WHERE ...

You could further parenthesize that, understanding that JOIN operators
bind left-to-right when not parenthesized, but I think it would just
add clutter not clarity.  Anyway, the point is that that first ON
clause can only refer to "u" and "d", because only those two tables
are in-scope for it.

There are other RDBMSes (mumble ... ancient mysql versions ... mumble)
that give the commas a different syntactic priority and would allow
that ON clause to reference "wl".  But they're wrong per SQL spec.

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] Config for fast huge cascaded updates

[Joshua D. Drake]

On 06/26/2017 06:29 PM, Andrew Sullivan wrote:

On Tue, Jun 27, 2017 at 10:17:49AM +1200, Craig de Stigter wrote:

We're doing a large migration on our site which involves changing most of
the primary key values. We've noticed this is a *very* slow process.




You can make it faster through a number of simple changes:

1. make sure synchronous_commit is off
2. make sure you have lots of checkpoint_segments (or a very large 
max_wal_size)
3. make sure you checkpoint_timeout is some ridiculously high value (2 
hours)


Alternatively, and ONLY do this if you take a backup right before hand, 
you can set the table unlogged, make the changes and assuming success, 
make the table logged again. That will great increase the write speed 
and reduce wal segment churn.


However, if that fails, the table is dead. You will have to reload it 
from backup.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, 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] Config for fast huge cascaded updates

[Andrew Sullivan]

On Tue, Jun 27, 2017 at 10:17:49AM +1200, Craig de Stigter wrote:
> We're doing a large migration on our site which involves changing most of
> the primary key values. We've noticed this is a *very* slow process.

Indeed.

Does the database need to be online when this is happening?

If it were me, I'd try to find a way to dump it, modify the data in a
dump file, and then reload it.  I think that'd be faster.

Another way you might try, if you need to be online while doing this,
is to write the whole thing into a new SQL schema.  Make the mods you
need.  When you think you're close to done, put a trigger in the "old
schema" to update data in the new schema, then do a last pass to catch
anything you missed in the interim, then cut your application over to
the new schema (update the search_path, force everything to disconnect
and reconnect, and when they reconnect they have the new data in
place).  A variation on this technique is also useful for gradual
roll-out of new features -- you don't have to upgrade everything at
once and you have a natural rollback strategy (but you need a more
complicated set of triggers that keeps the two schemas in sync during
cutover period).

This second approach isn't faster, it's hard on I/O and disk space,
but it keeps you up and you can do the changes at a leisurely pace.
Just make sure you have the I/O and space before you do it :)

Hope that helps,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] LEFT JOIN, entry can not be referenced

[David G. Johnston]

On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson 
wrote:

> SELECT
> wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl.
> docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue
> FROM worklogs AS wl, workactions AS wa, users AS u
> LEFT JOIN documents AS d ON wl.doc_id=d.id
> LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
> LEFT JOIN files AS f ON wl.file_id=f.id
> WHERE wl.action_id=wa.id AND wl.user_id=u.id
> ORDER BY wl.ts DESC;
>
>When I run this I get the error:
>
> 
> ERROR:  invalid reference to FROM-clause entry for table "wl"
> LINE 3: LEFT JOIN documents AS d ON wl.doc_id=d.id
> ^
> HINT:  There is an entry for table "wl", but it cannot be referenced
> from this part of the query.
> 
>

You should write out all of your joins explicitly.

FROM worklogs
JOIN workactions ON
JOIN users ON
LEFT JOIN documents ON
LEFT JOIN docrevs ON
LEFT JOIN files ON
--there were no non-join conditions in your where clause so it is omitted
here
ORDER BY

Mixing "FROM tbl1, tbl2 WHERE" and "FROM tbl1 JOIN tbl2 ON" syntax just
causes grief.

David J.

[GENERAL] LEFT JOIN, entry can not be referenced

[Jan Danielsson]

Hello,

   I'm trying to use LEFT JOIN's in a manner which I imagine is pretty
archetypal.  In short; I have a table called worklogs which has a few
columns that can't be NULL, and a few columns which may reference other
tables or will be NULL.  If the optional columns are not NULL I want to
use their id's to fetch names/titles from other columns.

   I use the following query to gather a list of the work log rows (this
query is much simpler than it looks at first glance; it just has a lot
of columns):

SELECT
wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl.docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue
FROM worklogs AS wl, workactions AS wa, users AS u
LEFT JOIN documents AS d ON wl.doc_id=d.id
LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
LEFT JOIN files AS f ON wl.file_id=f.id
WHERE wl.action_id=wa.id AND wl.user_id=u.id
ORDER BY wl.ts DESC;

   When I run this I get the error:


ERROR:  invalid reference to FROM-clause entry for table "wl"
LINE 3: LEFT JOIN documents AS d ON wl.doc_id=d.id
^
HINT:  There is an entry for table "wl", but it cannot be referenced
from this part of the query.


   This is at the outer bounds of my SQL knowledge; I understand what
the error and hint are saying (wl isn't valid in the context of the
JOIN), but I'm not sure how to remedy that / how to rephrase the query.


   ("Dear Diary"-moment: I've had queries in the past which work in
postgresql which I couldn't run in sqlite, but this is the first time I
can recall where a query works in sqlite but not in postgresql).

-- 
Kind regards,
Jan Danielsson



-- 
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] When inserting from a SELECT with an ORDER BY, are the inserts (and associated triggers) applied in order?

[Jim Fulton]

On Mon, Jun 26, 2017 at 5:43 PM, Tom Lane  wrote:

> Jim Fulton  writes:
> > When inserting from a SELECT with an ORDER BY, are the inserts (and
> > associated triggers) applied in order?
>
> Yeah, I'd expect so.  I'm not sure we'd promise that that will always
> remain true, but I can't think why it would be violated at the moment.
>
> > It looks like inserts aren't applied in order, and I'm wondering if this
> is
> > something I should expect.
>
> Hard to comment on that without seeing your test case.
>

Yup.  This works as I'd expect in my test case.  It's in the wild that I'm
having trouble. :(

At the risk of TMI (don't feel obliged to follow), I have a database with a
JSONB column that represents object data (http://newtdb.org). I have an
application in which the data are hierarchically organized.  At the
(almost) top level are "communities". I want to be able to search by
community and I want the search to be indexed on community id.  In this
application, ids for ancestor objects are always lower than ids of
descendents.  While objects may rarely move around in the hierarchy, their
communities never change.  Objects are sometimes created in the same
transaction as their parents. I use a trigger to find and copy community
ids into the JSONB data records and then index the JSONB properties.

A test Python script that simulates this:
https://gist.github.com/jimfulton/317e36e6f74c309ee9198f453c41ab59. Note
that objects are initially copied to a staging table and then copied in
mass to the data table. If the test script is run, all of the records have
"cid" properties set properly. If I remove the "order by" on line 80, then
some record end up without "cid" properties.

The non-test case is a bit more complicated.  If you're curious:

   - the insert logic:
   https://github.com/newtdb/db/blob/master/src/newt/db/_adapter.py#L67
   It uses upsert rather than deletion+insert to do updates.
   - The trigger and function for finding community ids:
   https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L327

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Config for fast huge cascaded updates

[Tom Lane]

Craig de Stigter  writes:
> We're doing a large migration on our site which involves changing most of
> the primary key values. We've noticed this is a *very* slow process.

> Firstly we've set up all the foreign keys to use `on update cascade`. Then
> we essentially do this on every table:

> UPDATE TABLE users SET id = id + 100;

> Since this cascades via about 40 foreign keys to most of the other tables
> in the database, this update on our fairly small table takes about five
> hours.

Do you have indexes on all the referencing columns?

The core problem here is that the updates will be cascaded one row at a
time.  As long as the referencing rows can be found by an indexscan,
that might be tolerable, but it's certainly not as fast as a bulk
update.

If you can guarantee no other updates while you're doing the migration,
it might be practical to drop the foreign key constraints, run all the
bulk updates by hand (on referencing tables too!), and then re-establish
the constraints.  Of course there's a lot of potential for errors of
omission here, but if you can script it and test the script in advance,
it's worth considering.

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

[GENERAL] Config for fast huge cascaded updates

[Craig de Stigter]

Hi folks

We're doing a large migration on our site which involves changing most of
the primary key values. We've noticed this is a *very* slow process.

Firstly we've set up all the foreign keys to use `on update cascade`. Then
we essentially do this on every table:

UPDATE TABLE users SET id = id + 100;


Since this cascades via about 40 foreign keys to most of the other tables
in the database, this update on our fairly small table takes about five
hours.

This is understandable (it's rewriting most of the database) but what
settings can we tweak to make this process faster?

So far we have experimented with the following:

   - checkpoint_timeout : 3600
   - autovacuum: 0
   - max_wal_size: 128 (2GB)
   - synchronous_commit: off

What other things would you recommend to improve performance of this sort
of thing?


-- 
Regards,
Craig

Developer
Koordinates

+64 21 256 9488 <+64%2021%20256%209488> / koordinates.com / @koordinates

Re: [GENERAL] ERROR: query returned no rows

[Adrian Klaver]

On 06/26/2017 01:10 PM, Paul A Jungwirth wrote:

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
 wrote:

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:

Perhaps
you should see what is line 85 when you do `\sf words_skip_game` (rather
than line 85 in your own source code).


Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]


I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)


This:

"It's how I have learned a lot deal about Postgres. "

should be:

"It's how I have learned a lot about Postgres. "

The hazards of editing on the fly.



Paul




--
Adrian Klaver
adrian.kla...@aklaver.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] ERROR: query returned no rows

[Adrian Klaver]

On 06/26/2017 01:10 PM, Paul A Jungwirth wrote:

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
 wrote:

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:

Perhaps
you should see what is line 85 when you do `\sf words_skip_game` (rather
than line 85 in your own source code).


Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]


I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)


The docs are extensive and in a constant state of change, so the list is 
 a great resource for finding information you haven't stumbled across 
yet on your own. It's how I have learned a lot deal about Postgres.




Paul




--
Adrian Klaver
adrian.kla...@aklaver.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] When inserting from a SELECT with an ORDER BY, are the inserts (and associated triggers) applied in order?

[Tom Lane]

Jim Fulton  writes:
> When inserting from a SELECT with an ORDER BY, are the inserts (and
> associated triggers) applied in order?

Yeah, I'd expect so.  I'm not sure we'd promise that that will always
remain true, but I can't think why it would be violated at the moment.

> It looks like inserts aren't applied in order, and I'm wondering if this is
> something I should expect.

Hard to comment on that without seeing your test case.

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] ERROR: query returned no rows

[Paul A Jungwirth]

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
 wrote:
> On 06/26/2017 12:03 PM, Paul Jungwirth wrote:
>> Perhaps
>> you should see what is line 85 when you do `\sf words_skip_game` (rather
>> than line 85 in your own source code).
>
> Or easier yet:
>
> https://www.postgresql.org/docs/9.5/static/app-psql.html
> "
> \ef [ function_description [ line_number ] ]

I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)

Paul


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

[GENERAL] When inserting from a SELECT with an ORDER BY, are the inserts (and associated triggers) applied in order?

[Jim Fulton]

When inserting from a SELECT with an ORDER BY, are the inserts (and
associated triggers) applied in order?

It looks like inserts aren't applied in order, and I'm wondering if this is
something I should expect.

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] ERROR: query returned no rows

[Adrian Klaver]

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:

On 06/26/2017 11:21 AM, Alexander Farber wrote:

The error message returned by
the database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
SQL statement

When I look at my source code
( https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at
the line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:


I agree the line numbers for error messages inside of plpgsql functions 
never seem to make sense, so I wouldn't get too hung up on line 85. 
Perhaps you should see what is line 85 when you do `\sf words_skip_game` 
(rather than line 85 in your own source code). But mostly I would try to 
find some other way of locating the cause of the error.


Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]

This command fetches and edits the definition of the named 
function, in the form of a CREATE OR REPLACE FUNCTION command. Editing 
is done in the same way as for \edit. After the editor exits, the 
updated command waits in the query buffer; type semicolon or \g to send 
it, or \r to cancel.


The target function can be specified by name alone, or by name and 
arguments, for example foo(integer, text). The argument types must be 
given if there is more than one function of the same name.


If no function is specified, a blank CREATE FUNCTION template is 
presented for editing.


If a line number is specified, psql will position the cursor on the 
specified line of the function body. (Note that the function body 
typically does not begin on the first line of the file.)


"

So:

\ef words_skip_game 85



You can read about the "query returned no rows" message here:

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html

It seems to me you should only see it for `INTO STRICT`, not plain 
`INTO`. I see from your gist that your function *does* have some queries 
that are `INTO STRICT`, so I would focus on those.


That page also describes how to use `print_strict_params` to get a 
little more info about the details of the error.


I hope that helps!

Paul





--
Adrian Klaver
adrian.kla...@aklaver.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] ERROR: query returned no rows

[Paul Jungwirth]

On 06/26/2017 11:21 AM, Alexander Farber wrote:

The error message returned by
the database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
SQL statement

When I look at my source code
( https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at
the line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:


I agree the line numbers for error messages inside of plpgsql functions 
never seem to make sense, so I wouldn't get too hung up on line 85. 
Perhaps you should see what is line 85 when you do `\sf words_skip_game` 
(rather than line 85 in your own source code). But mostly I would try to 
find some other way of locating the cause of the error.


You can read about the "query returned no rows" message here:

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html

It seems to me you should only see it for `INTO STRICT`, not plain 
`INTO`. I see from your gist that your function *does* have some queries 
that are `INTO STRICT`, so I would focus on those.


That page also describes how to use `print_strict_params` to get a 
little more info about the details of the error.


I hope that helps!

Paul


--
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: query returned no rows

[Alexander Farber]

In my case _opponent was NULL and there are no records in words_users with
PK uid being NULL... so that was the reason.

Thank you

Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

Hi again,

On Mon, Jun 26, 2017 at 8:21 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> words=> select * from words_skip_game(1, 1);
> ERROR:  query returned no rows
> CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
> SQL statement
>
> When I look at my source code ( https://gist.github.com/afarber/
> cac9a83b7a37307ace8d787be9b8ff4c ) at the line 85, then I am not sure if
> the line number reported by the error message is correct
>


I have added many RAISE NOTICE '' lines and now see that the error is
at SELECT ... INTO STRICT (which was not the line 85):

SELECT
_opponent,
fcm,
apns,
sns,
CASE
WHEN _skips = 5 THEN 'Противник пропускает
ход (еще один пропуск завершит игру)'
ELSE 'Противник пропускает ход'
END
FROMwords_users
WHERE   uid = _opponent
INTO STRICT
out_uid,
out_fcm,
out_apns,
out_sns,
out_note;
RETURN NEXT;

Regards
Alex

Re: [SPAM] [GENERAL] ERROR: query returned no rows

[Alexander Farber]

On Mon, Jun 26, 2017 at 8:39 PM, Moreno Andreo 
wrote:

> Il 26/06/2017 20:21, Alexander Farber ha scritto:
>
>>
>> RETURNING
>> player1,
>> score2,
>> score1
>> INTO
>> _opponent,
>> _score1,   -- the line 85
>> _score2;
>>
>> Is it intentional to return score2 in score1 and vice versa?



>
> Yes, Andreo, that one is intentional, but thank you.
>
> In all my custom functions I return player1, played1, hand1 for the
> calling player (even if it is player2 in the words_games table)
>

Regards
Alex

Re: [SPAM] [GENERAL] ERROR: query returned no rows

[Moreno Andreo]

Il 26/06/2017 20:21, Alexander Farber ha scritto:


RETURNING
player1,
score2,
score1
INTO
_opponent,
_score1,   -- the line 85
_score2;


Is it intentional to return score2 in score1 and vice versa?



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

[GENERAL] ERROR: query returned no rows

[Alexander Farber]

Good evening,

with PostgreSQL 9.5 I have extended a larger custom function, which has
worked well before and my problem is that the error message returned by the
database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at SQL
statement

When I look at my source code (
https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at the
line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:

UPDATE words_games SET
finished = _finished,
played2  = CURRENT_TIMESTAMP
WHERE
gid  = in_gid AND
player2  = in_uid AND
-- game is not over yet
finished IS NULL  AND
-- and it is second player's turn
(played2 IS NULL OR played2 < played1)
RETURNING
player1,
score2,
score1
INTO
_opponent,
_score1,   -- the line 85
_score2;

And here is my words_games table:

words=> \d words_games
   Table "public.words_games"
  Column  |   Type   | Modifiers

--+--+---
 gid  | integer  | not null default
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer  | not null
 player2  | integer  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer  | not null
 score2   | integer  | not null
 hand1| character(1)[]   | not null
 hand2| character(1)[]   | not null
 pile | character(1)[]   | not null
 letters  | character(1)[]   | not null
 values   | integer[]| not null
 bid  | integer  | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

And the record for the gid=1 (apologies if I copy-paste too much data here):

words=> select * from words_games where gid=1;
 gid |created| finished | player1 | player2 |
played1 | played2 | score1 | score2 |  hand1  |  hand2  |

pile

 |






  letters







 |







 values






   | bid

Re: [GENERAL] Configure Qt Creator to work with PostgreSQL to extensions development

[Arthur Zakirov]

On Sunday, 25 June 2017 17:34:11 MSK, Fabiana Zioti wrote:
> I'm creating an extension to PostgreSQL, with user-defined types and
> user-defined functions.
> 
> Extensions can be written in C as well as C ++, correct?
> I am currently using ATOM to develop in Ubuntu. But I would like to work
> with Qt.
> 
> PostgreSQL provides the tutorial for working with eclipse:
> https://wiki.postgresql.org/wiki/Working_with_Eclipse#
> 
> Is there any tutorial for Qt? Or are the setup steps similar to Eclipse?
> 
> Thank you very much in advance.

I am using QtCreator currently. I tried Atom and Sublime Text. But QtCreator 
is more convenient to me.

In short you need to do the following things:
1 - setup code style, use tabs instead of spaces, you can setup 
autoindentation
2 - create QtCreator project for PostgreSQL and your extensions by the 
following way:
  - select File > New File or Project > Import Project > Import Existing 
Project, and choose PostgreSQL or extension folder
  - in next step choose all files
  - QtCreator will create 3 files: *.config, *.files, *.includes
  - if you created project for an extension you need to define path to 
PostgreSQL include headers. Open *.includes file and write the path there.

With QtCreator you can quickly search necessary files or methods.
https://www.dropbox.com/s/r2b7vgt0wh5ibpm/qtcreator.png?dl=0

I didn't setup other options, because I make PostgreSQL and extensions from 
command line. I debug them using dbg, but you can do it also from QtCreator.

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


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