Re: How to keep format of views source code as entered?
On Saturday, January 9, 2021, raf wrote: > > Actually, I just had a look at the pg_views system > catalog where the source code for views is stored, and > it doesn't seem to contain enough information to > reconstruct a create view statement. It only contains > these columns: > > schemaname > viewname > viewowner > definition > > But definition is just the query itself. > > There is no list of column names (like there is with > procedures in pg_proc). > > Is all of that information stored somewhere else in the > system catalogs? > Views are relation-like and thus are primarily recorded on pg_class. David J.
Re: How to keep format of views source code as entered?
On Sat, Jan 09, 2021 at 02:22:25PM +, "Markhof, Ingolf"
wrote:
> Tom, all,
>
> when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL
> client) marks views that refer to the table using the old column name
> as erroneous. So, I can easily identify these cases. And of course
> I, as a user, I am acting in my context, i.e. my schema. So it is
> perfectly clear what table I am referring to.
>
> Please note: I am not developing any PL/SQL code. I don't have big
> development projects. I have more the role of an data analyst. I
> just create rather complex SQL queries which, from time to time, may
> need to be adopted to some new requirements. Or peers want to (re-)
> use (part of) my SQL queries. There is not really much versioning
> required.
>
> What I understood so far is: I can use e.g. DBeaver to interact with
> PostgreSQL, to develop my SQL code. But I finally need to copy the SQL
> code into e.g. Github. Which is a manual process. I'd mark the SQL
> code in the DBeaver editor window and copy it into some file in
> e.g. GitHub. Using Github, I'd get version control and other enhanced
> collaboration features which I don't really need. At the price that
> code transfer from SQL (DBeaver) to the code repository and vice versa
> is complete manually?! This doesn't really look like an enhancement.
>
> Most likely, there are more professional ways to do that. I'd be glad
> to get advice.
>
> What I would like to have is something that would automatically update
> the SQL code in the software repository when I run a CREATE OR REPLACE
> VIEW.
>
> Ingolf
Hi,
If there is a software repository, then I would
recommend considering not using tools like DBeaver to
develop your SQL views. Instead, develop them in
relation with the repository tools, and use psql or
similar to load the view into the database(s).
Alternatively, if you do modify the views "live" in the
databse, get (or have someone create) a tool to fetch
the code of the view from the database, and write it to
a file that can be committed into the repository.
It doesn't have to be copy and paste. A simple program
can be written to extract view source code and write it
to a file. Perhaps your colleagues that want to re-use
your source code can implement it. Such a tool would be
useful with or without a repository.
Here's an example of such a query but it's for
procedures/functions, and would need to be
very different for views.
select
p.proname, -- name
p.proretset, -- returns setof?
p.proisstrict, -- strict 't' or 'f'
p.provolatile, -- volatile or stable 'v' or 's'
p.prosecdef, -- security definer 't' or 'f'
p.pronargs, -- number of in arguments
p.prorettype, -- return type
p.proargtypes, -- space-separated list of in arg types
p.proallargtypes, -- array of in/out arg types (iff there are out args)
p.proargmodes, -- array of in/out arg modes like {i,o,o} (iff there are
out args)
p.proargnames, -- array of in/out arg names like {id,code,name}
p.prosrc, -- source code
cast(cast(p.oid as regprocedure) as text) -- nice signature
from
pg_user u,
pg_proc p
where
u.usename = current_user and
p.proowner = u.usesysid and
p.proname like 'myfunc_%' -- Your naming convention
order by
p.proname
The above returns enough information to construct a
corresponding create function statement (except for any
knowledge of precision and scale of numeric parameters).
Actually, I just had a look at the pg_views system
catalog where the source code for views is stored, and
it doesn't seem to contain enough information to
reconstruct a create view statement. It only contains
these columns:
schemaname
viewname
viewowner
definition
But definition is just the query itself.
There is no list of column names (like there is with
procedures in pg_proc).
You can tell the difference between a temporary and
non-temporary view because the schemaname is different
for temporary views (e.g. pg_temp_3, rather than
public).
I don't know if you could tell whether a view is
recursive or not.
And it doesn't look like you can determine if a view
has a local or cascaded check_option parameter, or the
security_barrier parameter.
Is all of that information stored somewhere else in the
system catalogs?
Without them, this query would only find the names and
query code of views:
select
v.viewname,
v.definition
from
pg_views v
where
v.viewname like 'myview_%'; -- Your naming convention
Is there a query that can be used to obtain all of the
information needed to reconstruct the create view
statement that corresponds to a view in pg_views?
cheers,
raf
Re: How to keep format of views source code as entered?
Markhof, Ingolf writes: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL > client) marks views that refer to the table using the old column name as > erroneous. So, I can easily identify these cases. And of course I, as a user, > I am acting in my context, i.e. my schema. So it is perfectly clear what > table I am referring to. > > Please note: I am not developing any PL/SQL code. I don't have big > development projects. I have more the role of an data analyst. I just create > rather complex SQL queries which, from time to time, may need to be adopted > to some new requirements. Or peers want to (re-) use (part of) my SQL > queries. There is not really much versioning required. > > What I understood so far is: I can use e.g. DBeaver to interact with > PostgreSQL, to develop my SQL code. But I finally need to copy the SQL code > into e.g. Github. Which is a manual process. I'd mark the SQL code in the > DBeaver editor window and copy it into some file in e.g. GitHub. Using > Github, I'd get version control and other enhanced collaboration features > which I don't really need. At the price that code transfer from SQL (DBeaver) > to the code repository and vice versa is complete manually?! This doesn't > really look like an enhancement. > > Most likely, there are more professional ways to do that. I'd be glad to get > advice. > > What I would like to have is something that would automatically update the > SQL code in the software repository when I run a CREATE OR REPLACE VIEW. > Even with Oracle, I found it works much better to keep all your DDL/DML in files stored within the OS file system. This approach also works fine with tools like DBeaver, pgAdmin, etc as all of these files also support working with files. The main benefits I find with this approach are - 1. Adding version control is easy. Doesn't matter if it is git, hg, bzr, svn or rcs - any version control system works fine. It doesn't have to be a cloud service like github, though some sort of centralised repository can be useful for managing things like backups and sharing code across a team (I've used gitlab hosted locally t great success). Most editors also have built-in support for common version control systems, so the additional overhead associated with using a version control system is very little. 2. Having all your code in version control makes tracking changes trivial. This is often really useful in tracking down problems/bugs caused by a change and other diagnostics. More than once, I have found I've gone down a bad path of changes and want to restore a previous version. Assuming you use your version control system appropriately, this becomes trivial. If your code is only in the db, once you make changes, the old code is gone and cannot easily be restored. 3. Having all the DDL/DML in files makes data migration very simple. I will typically have a development environment where I develop my DDL/DM which is separate from the production environment. This can be very important even in data analysis and data mining type applications as it allows you to develop complex and possibly resource hungry DML in an environment where mistakes won't impact production systems. It also means you can have a dev environment which is populated with specific data sets which have been defined to help in the development process e.g. perhaps smaller, so tests run faster or perhaps ensuring all possible data permutations are included etc. If all your DDL/DML are in files, seting up a new environment is as simple as writing a basic script and using psql (pg) or sqlplus (oracle) to load the DDL/DML. In simpler environments, you can even use a naming scheme for the files which sets the order - loading the data then becomes as easy as 'psql *', avoiding the need to write scripts (even though writing the scripts is typically trivial). 4. Having all your DDL/DML in files allows you to use the many powerful text manipulation tools which exist on most platforms. While such tools are not often required, when they are, it can be a real bonus. Being able to use tools like sed, awk, perl, etc have saved my bacon more than once. Over the years, I have also built up a very useful library of techniques, templates etc. Being able to quickly and easily access this library is very useful. 5. While the built in editors in tools like DBeaver and pgAdmin are OK, I find they are rarely as good as my preferred editor and I often get frustrated at having to know/learn the editors of different tools. Having everything based on files means I can use my preferred editor, which has support for things like completion, familiar syntax highlighting and key bindings, templates etc. These days, many editors even have built-in support for popular databases like pgsql and oracle, so you can load the code and run it without having to leave your editor. Having the code in the database can be useful. I've used this in oracle to provide
Re: How to keep format of views source code as entered?
On Sat, Jan 9, 2021 at 9:22 AM Markhof, Ingolf < ingolf.mark...@de.verizon.com> wrote: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL > client) marks views that refer to the table using the old column name as > erroneous. So, I can easily identify these cases. And of course I, as a > user, I am acting in my context, i.e. my schema. So it is perfectly clear > what table I am referring to. > > This highlights two major differences between Oracle and Postgres. Postgres will never allow you to make an invalid view. Also, the search_path in Postgres acts quite differently from the user context in Oracle. There is no guarantee that a user has a schema in postgres or that the schema would be in the search_path. > Please note: I am not developing any PL/SQL code. I don't have big > development projects. I have more the role of an data analyst. I just > create rather complex SQL queries which, from time to time, may need to be > adopted to some new requirements. Or peers want to (re-) use (part of) my > SQL queries. There is not really much versioning required. > > What I understood so far is: I can use e.g. DBeaver to interact with > PostgreSQL, to develop my SQL code. But I finally need to copy the SQL code > into e.g. Github. Which is a manual process. I'd mark the SQL code in the > DBeaver editor window and copy it into some file in e.g. GitHub. > Using Github, I'd get version control and other enhanced collaboration > features which I don't really need. At the price that code transfer from > SQL (DBeaver) to the code repository and vice versa is complete manually?! > This doesn't really look like an enhancement. > > Most likely, there are more professional ways to do that. I'd be glad to > get advice. > > What I would like to have is something that would automatically update the > SQL code in the software repository when I run a CREATE OR REPLACE VIEW. > > If you want to use source control (and I think it's a good idea), look into something like flywaydb or liquibase or any of the many other db schema control frameworks. > Ingolf > > > > Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - > Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - > Vorsitzender des Aufsichtsrats: Francesco de Maio >
Re: Static memory, shared memory
Jack Orenstein writes: > I am writing a Postgres extension, and thought that I had memory > corruption, (thanks for the --enable-cassert lead). I might, but It now > looks like I need to understand the use of shared memory and locking in > Postgres. So I have two questions. > 1) I am now guessing that my original problem is caused by relying on > static memory in my extension (i.e., in the source declaring > PG_MODULE_MAGIC). This static memory is almost but not quite constant -- it > is initialized from _PG_init, and then never modified. I suspect that this > cannot work in general (since Postgres is multi-process), but I thought it > would be adequate for early development. However, I am seeing this static > memory get corrupted even when there is only a single process executing the > extension code (verified by examining getpid()). Define what you mean by "corrupted". It seems highly unlikely that any code but your own is touching this memory. Really the big-picture question here is what are you hoping to accomplish and why do you think this memory might need to be shared? regards, tom lane
Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it
On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston wrote: > Subqueries must be enclosed in parentheses. The parentheses that are part > of the function call do not count. > > Ah! Thank you David, this has worked now - CREATE OR REPLACE FUNCTION words_solve_puzzle( in_mid bigint, in_uid int, in_guess text, OUT out_json jsonb ) RETURNS jsonb AS $func$ DECLARE _tilejsonb; _letter char; _value integer; BEGIN in_guess := UPPER(in_guess); FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS((SELECT tiles FROM words_moves WHERE mid = in_mid)) LOOP _letter := _tile->>'letter'; _value := (_tile->>'value')::int; -- verify that all played tiles except wildcard are found in the suggested answer IF _value > 0 AND POSITION(_letter IN in_guess) = 0 THEN out_json := json_build_object( 'label',' Keep guessing!' ); RETURN; END IF; END LOOP; -- check if the in_guess is one of the played words in that move IF NOT EXISTS(SELECT 1 FROM words_scores WHERE mid = in_mid AND word = in_guess) THEN out_json := json_build_object( 'label',' Wrong!' ); RETURN; END IF; -- the solution already submitted, just ack, but do not award coins IF EXISTS (SELECT 1 FROM words_puzzles WHERE mid = in_mid AND uid = in_uid) THEN out_json := json_build_object( 'label',' Correct!', 'url', '/ws/puzzle2?mid=' || in_mid || '=' || MD5(in_mid || 'my secret') ); RETURN; END IF; -- save the puzzle solution and award coins to the user INSERT INTO words_puzzles (mid, uid, solved) VALUES (in_mid, in_uid, CURRENT_TIMESTAMP); UPDATE words_users SET coins = coins + 1 WHERE uid = in_uid; out_json := json_build_object( 'label',' Correct, +1 coin!', 'url', '/ws/puzzle2?mid=' || in_mid || '=' || MD5(in_mid || 'my secret') ); END $func$ LANGUAGE plpgsql; P.S. 'my secret' is not my real secret passphrase :-)
Static memory, shared memory
I am writing a Postgres extension, and thought that I had memory corruption, (thanks for the --enable-cassert lead). I might, but It now looks like I need to understand the use of shared memory and locking in Postgres. So I have two questions. 1) I am now guessing that my original problem is caused by relying on static memory in my extension (i.e., in the source declaring PG_MODULE_MAGIC). This static memory is almost but not quite constant -- it is initialized from _PG_init, and then never modified. I suspect that this cannot work in general (since Postgres is multi-process), but I thought it would be adequate for early development. However, I am seeing this static memory get corrupted even when there is only a single process executing the extension code (verified by examining getpid()). So the question is this: Is the use of non-constant static memory ill-advised, even assuming there is just one process relying on it? Or is it more likely that I still have run-of-the-mill memory corruption. (--enable-cassert hasn't notified me of any problems). 2) Assuming that I should be using shared memory instead of static, I am reading https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14, and examining contrib/pg_prewarm. The xfunc-c documentation mentions RequestNamedLWLockTranche to get an array of LWLocks. But the sample code that follows calls GetNamedLWLockTranche. And the pg_prewarm code doesn't rely on an array of locks, it initializes a single lock, (which I think would be adequate for my needs). I understand the purpose of locks for obtaining and manipulating shared memory but I am confused about the correct way to proceed. I'm guessing it is safe to assume that pg_prewarm works and is a good model to follow, and that the doc may be buggy. Can someone clarify my confusion, and perhaps point me at a tutorial on correct usage of the interfaces for LWLocks and shared memory, (I haven't been able to find one). Thank you. Jack Orenstein
Re: How to keep format of views source code as entered?
> On Jan 9, 2021, at 06:22, Markhof, Ingolf > wrote: > What I would like to have is something that would automatically update the > SQL code in the software repository when I run a CREATE OR REPLACE VIEW. I think you are approaching this backwards. The SQL in the repository should be the definitive version. If you wish to change the view, you change the CREATE OR REPLACE VIEW command that you have stored in the repository, and then apply that to the database so it now has the new view definition. You may not, in a small independent project, feel the need for a source code repository, but it becomes very useful very quickly. -- -- Christophe Pettus x...@thebuild.com
Select a column and then apply JSONB_ARRAY_ELEMENTS to it
On Saturday, January 9, 2021, Alexander Farber wrote: > > BEGIN > FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM > words_moves WHERE mid = in_mid) > > Subqueries must be enclosed in parentheses. The parentheses that are part of the function call do not count. David J.
RE: How to keep format of views source code as entered?
Tom, all, when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table using the old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting in my context, i.e. my schema. So it is perfectly clear what table I am referring to. Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an data analyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new requirements. Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required. What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finally need to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editor window and copy it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced collaboration features which I don't really need. At the price that code transfer from SQL (DBeaver) to the code repository and vice versa is complete manually?! This doesn't really look like an enhancement. Most likely, there are more professional ways to do that. I'd be glad to get advice. What I would like to have is something that would automatically update the SQL code in the software repository when I run a CREATE OR REPLACE VIEW. Ingolf Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
Select a column and then apply JSONB_ARRAY_ELEMENTS to it
Hello,
for a word puzzle using PostgreSQL 13.1:
https://wortefarbers.de/ws/puzzle2?mid=138=c6f469786df7e8d44461381b62b2ce7d
I am trying to improve a stored function -
CREATE OR REPLACE FUNCTION words_solve_puzzle(
in_mid bigint,
in_uid int,
in_answertext,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_tilejsonb;
_letter char;
_value integer;
_answer text;
BEGIN
FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM
words_moves WHERE mid = in_mid)
LOOP
_letter := _tile->>'letter';
_value := (_tile->>'value')::int;
RAISE NOTICE 'Tile % letter % value', _tile, _letter,
_value;
END LOOP;
However this results in the error message -
ERROR: 42601: syntax error at or near "SELECT"
LINE 24: ... FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT til...
^
LOCATION: scanner_yyerror, scan.l:1180
Could you please help me, how to combine SELECT query and the LOOP
through JSONB_ARRAY_ELEMENTS here?
Thank you
Alex
P.S: Here the table:
words_de=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Re: Suggestion: provide a "TRUNCATE PARTITION" command
Quoting Michael Lewis : Still, no feedback on the effect that a truncate call is having on the DB and may be doing more than intended fairly easily. I am not in the hackers group so I couldn't say this feature would not be implemented. It just seems unlikely given the philosophies of that group. I would not feel bad to have a more efficient option but possibly a more dangerous one. Projects/application could setup policies about what may be done in which way and what not. S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Threema (Safer than WhatsApp): A76MKH3J Handys: +41 78 947 36 21 | +49 1578 772 37 37 smime.p7s Description: S/MIME Signature
