Re: Creating complex track changes database - challenge!

Hi Lukasz I am working on a generic (reading the information schema and other database metadata), trigger based solution for SCD tables, i. e. tables that keep (or not according to SCD type) history of the data. However, it is not far grown and I am not having much time to advance it so i

Re: Creating complex track changes database - challenge!

I attached what I have got so far. I will setup a shared repository these days. Zitat von Thiemo Kellner : Hi Lukasz I am working on a generic (reading the information schema and other database metadata), trigger based solution for SCD tables, i. e. tables that keep (or not according to

Re: Creating complex track changes database - challenge!

You can access code with git clone ssh://@git.code.sf.net/p/pg-scd/code pg-scd-code and browse it at https://sourceforge.net/p/pg-scd/code/ On 02/27/18 08:43, Thiemo Kellner wrote: I attached what I have got so far. I will setup a shared repository these days. Zitat von Thiemo Kellner

Is there a way to get the name of the calling function in pgplsql?

Hi all In a function I would like to log the caller. Is there a way to get its name in pgplsql? Kind regards Thiemo -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF This message

Autonomous transaction, background worker

Hi I try to setup a logging facility and want it to do its work asynchronously in autonomous transactions. I have read http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html and chapter 47 of the documentation believing those articles are about the same. However

Re: Autonomous transaction, background worker

I've experimented with background workers a bit. Yes, normally you'd write some C, but you could also install this (mentioned in the blog post and the source of pg_background_launch): Thanks for your answer. I want my logger to be used with the facilities there are from PostgreSQL so I'll go t

dblink: could not send query: another command is already in progress

Hi all I try to use dblink to create a asynchronous logging facility. I have the following code -- open the dblink if it does not yet exist V_DBLINK_CONNECTION_NAME := GET_PROPERTY_VALUE_STRING( I_PROPERTY_NAME => 'DBLINK_CONNECTION_NAME'

Re: dblink: could not send query: another command is already in progress

On 03/30/18 07:39, Laurenz Albe wrote: psql:testing/test.pg_sql:41: NOTICE: Connection busy: 1 psql:testing/test.pg_sql:41: NOTICE: Last error: OK psql:testing/test.pg_sql:41: NOTICE: Cancel query: OK psql:testing/test.pg_sql:41: NOTICE: Connection busy: 0 psql:testing/test.pg_sql:41: NOT

Re: dblink: could not send query: another command is already in progress

On 03/30/18 11:14, Laurenz Albe wrote: You have to consume the result before you can send the next query. I changed implementation but still get the same error but now different context. I tried to retrieve the result but I failed I committed the last code to its project repository at Source

Re: dblink: could not send query: another command is already in progress

On 04/03/18 11:28, Laurenz Albe wrote: [...] psql:testing/test.pg_sql:42: ERROR: function WRITE_MESSAGE_TO_TABLE(i_function => text, i_message => text, i_level => text, i_present_user => name, i_session_user => name, i_transaction_timestamp => timestamp with time zone, i_transaction_id => bigi

dblink: give search_path

Hi all I try to execute a function not in the Schema I connect to with dblink. Is there way to tell dblink to set search_path in a specific way? I have not found a solution in the documentation. I tried with the set search_path definition in the function declarations to no avail. Function

psql variable to plpgsql?

Hi all Is there a way to pass the value of a psql variable into function code? I create a schema with help of psql variable \set SCHEMA_NAME LOGGER create schema :SCHEMA_NAME; I would like to create a function that has the Schema hard coded like declare V_SCHEMA_NAME text

Re: psql variable to plpgsql?

Zitat von Pavel Stehule : no. The :xxx is not evaluated inside string. The workaround is using GUC variables and related functions. Can be used from psql and from plpgsql too. Hi Pavel, thanks for pointing this out. However, I implemented another solution with dedicated PostgreSQL user where

rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

Hi all When running following query in psql (server and client version 10 with replication on Debian 9), I get the message mentioned in the subject. I have not found much searching the internet. There were suggestions on bloat so I ran "vacuum (verbose, full, analyze)" but the message remains.

array UNNESTed to rows stable with respect to order?

Hi all I have created following statement to get the ordered parameter list of functions. I use UNNEST to get rows from array. This works fine but I am not sure whether the ordering remains in the later use. Background is PL/pgSQL to revoke grants to get a pristine start for granting. As the

Re: array UNNESTed to rows stable with respect to order?

Zitat von Paul Jungwirth : I think you are looking for `WITH ORDINALITY` (in pg 9.4+). For instance you could rewrite your first CTE like so: Thanks for the hint. Kind regards -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF -

Re: Microsoft buys GitHub, is this a threat to open-source

Zitat von Achilleas Mantzios : Who hasn't missed sourceforge ? or ... freshmeat while we'are at it :) I am sticking to sourceforge still. I never understood what people made leave it. I was investigating a bit if I should move on to github too but I do not remember what prevented me from d

Re: Can you make a simple view non-updatable?

Zitat von Ryan Murphy : Is there any way to set a VIEW to be read-only -- specifically, can I do this for a view that is automatically updatable due to being simple? Without saying anything about if this is directly possible, using different users with appropriate grants Comes to my mind, i.

Re: Question about getting values from range of dates

Hi Mike Zitat von Mike Martin : I have entries for files in database which lack specific values (xml files not generated) These values can be obtained by what are called DTC read files, where the values are the same in the last DTC read file before date of file and the next DTC read file (by da

Re: Database name with semicolon

Zitat von "joby.john@nccgroup.trust" : Not sure what else I can try or whether the ODBC driver supports database name with a semicolon at all. Not knowing much about ODBC, how about single quotes? And if you pass the entire URL, would it be possible to pass instead values to Kind of functio

RFC on pglogger

Hi I am quite happy to announce the first release package of pglogger for structured logging to table and/or standard out. I crafted it inspired by log4j in the hope I could give back the community a bit. You find details at https://sourceforge.net/p/pglogger/wiki/Home/ Kind regards Thiemo

Re: sql questions

Zitat von haman...@t-online.de: a) I am running some select query select ... order by Now, I would like to preserver the ordering through further processing by adding a sequence number Of course I can do: create temp sequence mseq; select xx.*, nextval('mseq') as ord from (select ... ord

PostgreSQL intenal scheduler?

Hi all I am designing a framework for historisation implementation (SCD). One feature I would like to provide is a table in that the actual state of an entity is put and if this is complete, this history table is "updated": - ==> | ENTITY_ACT | =

Re: PostgreSQL intenal scheduler?

I have seen pg_cron but it is not what I am looking for. It schedules tasks only by time. I am looking for a fifo queue. pg_cron neither prevents from simultaneous runs I believe. Quoting Thomas Kellerer : There is no built-in scheduler, but there is an extension that supplies that https:/

Re: PostgreSQL intenal scheduler?

This is a queue but if I am not mistaken, it is outside PostgreSQL where to go I am very reluctant. I will look at it in more depth. Thanks! Quoting Ron : Maybe https://github.com/chanks/que is what you need. On 09/05/2018 02:35 PM, Thiemo Kellner wrote: I have seen pg_cron but it is not

Re: PostgreSQL intenal scheduler?

Because I am looking for a fifo queue and not for time schedule. Thanks anyway. Quoting Tim Clarke : Why not just call your "do a scheduled run" code from cron? Tim Clarke

Re: PostgreSQL intenal scheduler?

Thanks for the hint. Your solution seems to be good. However, I am designing my framework for fun, to give back something to the community and because I think it's time that historisation is not reinvented and reimplemented again and again. Having that said, I think I can get rid of the nee

Privilege mess?

Hi all I installed pglogger (https://sourceforge.net/projects/pglogger/) and try to insert into the "level" table as user "act" but it fails claiming insufficient privileges even though insert is granted to public (see below). What am I missing? Kind regards Thiemo thiemo @ thiemos-to

Re: Privilege mess?

Quoting Christoph Moench-Tegeder : Schema privileges. provileges. I'd guess you miss USAGE on schema logger. Thanks for the hint. I did not know about a usage grant coming from Oracle, so I completely ignored the possibility of the absence of a different grant. I just read the documentat

Re: Privilege mess?

Quoting "David G. Johnston" : Layers of security.  But yes it is generally sufficient enough to simply allow usage on scheme without much thought while ensuring contained objects are sufficiently secured. Thanks :-)

Re: What is the problem with this code?

In your place I would double check whether the table structure on the database is what you expect. Without knowing the code of mentioned function there seem only two numbers in the call. It quite misty in the crystal fortune telling ball to me. Quoting Igor Korot : Does anybody have an id

timestamp out of range while casting return value to function's return type

Hi all I created a function that ought to return a timestamptz (another likewise timestamp) but calling it, I get mentionied error. What do I miss? I tried to return a timestamp of the year 2000 to no avail. Call: select utils.get_max_timestamptz(); -- Function -- create or replace functio

Re: timestamp out of range while casting return value to function's return type

Hi Tom Thanks for replying so fast. You are absolutely right. I changed the code file but failed to install it. :-( I am sorry for not checking the obvious. Kind regards Thiemo Quoting Tom Lane : Thiemo Kellner writes: I created a function that ought to return a timestamptz (another

Re: timestamp out of range while casting return value to function's return type

Karsten Hilbert : On Sat, Aug 24, 2019 at 12:57:07AM +, Thiemo Kellner wrote: Call: select utils.get_max_timestamptz(); -- Function -- create or replace function GET_MAX_TIMESTAMPTZ() returns timestamptz language plpgsql immutable -- Include the hosting schema into search_path so

Re: pgmodeler ?

Quoting Olivier Gautherot : This is the specific error message: Could not execute the SQL command. Message returned: ERROR: column pr.proisagg does not exist LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag... ^ HINT: Perhaps you meant to reference the column -- "They

Re: Posible off topic ? pgmodeler

Quoting stan : What I am trying to do, at the moment is get a complete understanding of their bossiness model, regarding the source code for this project. Thanks for any input on this. To the best of my knowledge Raphael tries to fund its time on the development of pgmodeler with selling

Use of ?get diagnostics'?

Hi all I try to create a function (code at https://pastebin.com/mTs18B90) using 'get diagnostics' to retrieve the number of affected rows. However, it throws the following exception was thrown: SQLSTATE: 42703 column "row_count" does not exist when I test it with drop table if exists TE

Re: Use of ?get diagnostics'?

Hi Andrew Paste sites are for IRC, on the mailing list you should always attach the necessary details to your message. Ok, I was under the impression that paste site were preferable to attachments which generates traffic not everyone is interested in. Thiemo> the following exception wa

Re: Use of ?get diagnostics'?

Hello Adrian Quoting Adrian Klaver : To get above I believe you will need to use GET CURRENT DIAGNOSTICS PG_CONTEXT: I actually use "get stacked diagnostics" to retrieve the exception place. And it works. I am not sure why I did no see it. However, I noticed, that the stack does not incl

pgutils, pglogger and pgutilsL out

Hi all I do not mean to spam so please tell me if this is not the right place for release announcements of OSS software for PostgreSQL. Be it as may, I am happy to have: - pgutils out: providing very basic functionality for PostgreSQL base applications (https://sourceforge.net/p/pgutils/

Re: SQL pretty pritner?

https://www.sqlinform.com/ Quoting stan : I have a presentation later in the week, and i plan on printing out some files containing SQL commands. I have used some "pretty printers" in the past for things like Perl scripts. What I am thinking of s something that bolds keywords, handles page brea

Possible trigger bug? function call argument literalised

Hi I created a table with trigger and according trigger and trigger function as drop table if exists CALCULATION_METHOD cascade; create table CALCULATION_METHOD (ID uuid not null default uuid

Re: Possible trigger bug? function call argument literalised

end if; insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*); return NULL; end; $body$; Running this version, I get another proof that the term was literalised: psql:common_calculation_method_insert.pg_sql:59: ERROR: Schema "current_schema" could not be foun

Re: Possible trigger bug? function call argument literalised

Quoting Rob Sargent : The function definition doesn’t name any parameters? Nope, trigger functions cannot, according to documentation. Parameters can be passed as list/array of values though. I have no clue about why this needs to be so awful/awesome. -- S/MIME Public Key: https:

Re: Possible trigger bug? function call argument literalised

Quoting Tom Lane : Thiemo Kellner writes: create trigger CALCULATION_METHOD_BR_IU before insert on CALCULATION_METHOD for each row execute function METHOD_CHECK(current_schema); Executing such, the string "current_schema" gets literalised, i.e. single quoted: Yu

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : Well, I guess, I can put the correct schema at installation, but would have liked to have a more general approach. Furthermore, I think this also implies that installation can only be done by psql. :-s Why not grab the CURRENT_SCHEMA in the function?: DECLARE

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : Can you provide an outline form of what you are trying to accomplish? Hm, making myself understood. ;-) So from the very beginning. There is the concept of growing degree days (https://en.wikipedia.org/wiki/Growing_degree-day). It is a measure for energy an organis

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : Familiar with it, I have worked in farming(outdoor and indoor(greenhouse)) industries. Cool (https://en.wikipedia.org/wiki/Growing_degree-day). It is a measure for energy an organism can consume in a specific day for its development. Also used to anticipate pe

Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver : On 1/3/21 1:44 PM, Thiemo Kellner wrote: So is the below still only going to fire on INSERT? If so it will not deal with functions that disappear after the INSERT, which in the end makes it similar to my suggestion:) The point being you are taking a snapshot in

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

Re: Do we need a way to moderate mailing lists?

Also, could it be possible to make messages plain text? I see a lot of varying fancy fonts and I hate that. I even hate it more when people post messages not properly trimmed or messages that need formatting preserved such as select output, i.e. table data, explain plans, etc. Proportiona

Re: How to check if a materialised view is being updated?

I’ve got a materialized view as a source for my ETL-process, and the materialized view takes several hours to refresh. During which it is locked for queries. Would it be an option to split the process into a cascade of materialized views to minimize the actual time of lock? So I’m lookin

Re: Window function?

Hi Robert Interesting problem. I need to think about it. You need to figure out when Input changes. You can achieve this by using lead or lag (depending of the sort direction over start) https://www.postgresql.org/docs/current/functions-window.html . Hope this nudges you to a solution. Kind

Re: an difficult SQL

Hi Rafal You first could select the three users with the most recent entries with a windowing function (https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) putting it into a with query (https://www.postgresql.org/docs/15/sql-select.html), in following with querie

Is there something wrong with my test case?

Hi all and merry Christmas I was under the impression that updating a table with values from (an) other table(s) would be implemented most efficiently with a correlated subquery a long the schema as follows, let's name it A. update TO_BE_UPDATED U set ({column list}) = ({correlated subqu

Is it impolite to dump a message

Hi all I posted a question and did not get a reaction. Now I wonder whether no one took interest (no offence meant) no one has an answer or whether the point in time I posted was just rubbish. In the latter case I would be inclined to dump/repost my question but only if this was not impol

Re: Is there something wrong with my test case?

Hi HP Thanks for your reply. Quoting "Peter J. Holzer" : On 2018-12-25 11:54:11 +0000, Thiemo Kellner wrote: [three different but functionally equivalent queries] Explain analyze verbose showed for: A (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117 rows=0 loops=

Re: Is there something wrong with my test case?

Hi David Thanks for your revision. Quoting David Rowley : On Wed, 26 Dec 2018 at 00:54, Thiemo Kellner wrote: Explain analyze verbose showed for: A (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117 rows=0 loops=1) B (cost=264.72..974.25 rows=31 width=58) (actual time=1.508

Re: Is there something wrong with my test case?

Hi Fabio Quoting Fabio Pardi : The cost is not a range. The 2 numbers you see are: * Estimated start-up cost. This is the time expended before the output phase can begin, e.g., time to do the sorting in a sort node. *Estimated total cost. This is stated on the assumption that

Re: POSTGRES/MYSQL

Quoting Adrian Klaver : On 3/11/19 9:31 AM, Sonam Sharma wrote: Hi Adrian, Ours is retail company and the DB size is Max 30gb, currently we are using db2. Things to consider: 1) Migration tools for DB2 --> MySQL/Postgresql. I have not done this, so someone else will have to comment.

Table inheritance over schema boundaries possible

Hi all I am wondering if table inheritance is possible over the boundaries of schemata and different owners. I have database act with schemata il and scd. When I issue as IL create table IL.INHERITANCE_TEST() inherits (SCD.TEMPL_BK); I get [Code: 0, SQL State: 42501] ERROR: permission den

Re: Table inheritance over schema boundaries possible

Quoting Achilleas Mantzios : as of 10 (same with 11) you (IL) must be the owner of relation SCD.TEMPL_BK . Create your tables with a user who has correct privileges on both tables/schemas. Not what I hope for but was afraid of. thank you for the answer. -- Achilleas Mantzios IT DEV Lead I

Re: sequences

Hi Karl I did not double check with the doc whether the SQL you posted is valid (I guess it could by applying the defaults) however I do not see how sequences would govern the sending of data to users. Kind regards Thiemo Quoting Karl Martin Skoldebrand : Hi, I'm trying to troubleshoot

Re: Need a DB layout gui

You also could try out DBVisualizer (https://www.dbvis.com/). It is available in a feature reduced free version. Feautre comaprison is at https://www.dbvis.com/features/feature-list/. I personally bought the pro to support development. I rarely use a pro feature. Quoting Zahir Lalani : Nav

Purely declarative FKs

Hi Please bear with me, if this is the wrong place or this is an old question answered over and over again. Context: In my professional life I rarely come across projects/applications where there are foreign keys on the database. This is due to loading freedom not due to that there actually

Re: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly

Hi Why do you need checksums? Can you not employ a full outer join? My though behind this is that checksumming is quite costly cpuwise and you have to fiddle with each and every relevent column, be it as part of the checksum string be it as part of the joiner. The joiner would have the advant

Rule system (and triggers)

Hi all I am afraid, I have not understood the rule system yet. I have got 4 tables and a view over all of them. ladevorgaenge  kanton  tarifgruppe 0..1   \       |  /        \  | /         \ |    / |    |    |     /|\ /|\ /|\   tarif_progress

Detection of which attributes should get set in update trigger

Hi all I have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be left alone. Is there a

Re: Detection of which attributes should get set in update trigger

Thanks for the reply. I confirm the behaviour. Your explanation makes sense if I consider having read that with an update the is a complete new record version written. Am 10.11.2023 um 14:35 schrieb David G. Johnston: On Friday, November 10, 2023, Thiemo Kellner wrote: Hi all I

Conditional compilation

Hi Does PostgreSQL have something like Oracle's conditional compilation? This is sort of an if then statement that gets evaluated on compilation/installation time of PL/SQL code. If the condition is met, the code until the $END gets compiled. It is even possible to switch on/off parts of sing

Re: Conditional compilation

checks for every and each logging statement put (as the check has been done on installation already). begin     do_something;     $if check_if_env_is_dev $then         do_some_logging;     $end     do_more_stuff; end; Am 12.11.2023 um 16:58 schrieb Tom Lane: Ron writes: On 11/12/23 09:

Re: client/server versions

Hi Dick Out of curiosity, what is the reason there is this zoo of versions. Is it impossible to align them to one version? Cheers Thiemo

Re: client/server versions

Am 21.11.2023 um 13:18 schrieb Dick Visser: It is possible, it's just that there is no real need to at the moment, ao we have not spent that much effort on the topic. At any time there will always be newer upstream versions. Ok, fair enough, but it puzzles me that the effort shall be put int

pPL/pgSQL restriction on characters for copying types?

Hi My names can contain a special character (⠒), e.g. to separate the donator object from the name or the name from the type. Typically, I use this with foreign keys, e.g. table PARENT has the surrogate key ID, the table CHILD would then have the column PARENT⠒ID. That way, I can use the unde

Re: pPL/pgSQL restriction on characters for copying types?

Oh, I totally forgot to mention that I ran the scripts with DbVisualizer against a 16.1 (Debian 16.1-1.pgdg110+1) server using PostgreSQL JDBC Driver 42.6.0 . Am 26.02.2024 um 16:51 schrieb Thiemo Kellner: Hi My names can contain a special character (⠒), e.g. to separate the donator object

Re: pPL/pgSQL restriction on characters for copying types?

Thanks for the hint and care. The install script has a set statement already and I now added the search_path clause to no avail. Please find the entire code attached and a screenshot from the error. Am 26.02.2024 um 17:35 schrieb Tom Lane: Thiemo Kellner writes: However, I want to create a

Re: pPL/pgSQL restriction on characters for copying types?

Shame on me. My bad. It was the order of installation that did not work. Sorry for that. I was mislead by the error message. If an object is missing I would not expect an invalid type name message. Thanks Am 26.02.2024 um 17:53 schrieb Thiemo Kellner: Thanks for the hint and care. The install

Me again with an insert trigger problem

Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "Drop-

Re: Me again with an insert trigger problem

Thanks. 27.02.2024 19:09:50 Adrian Klaver : > > On 2/27/24 9:49 AM, Thiemo Kellner wrote: >> Hi >> >> I am surprised that my before insert trigger function does not insert any >> rows into NODE_GOOD. >> >> I was under the impression that the trigger

Re: Me again with an insert trigger problem

Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME   from NODE⠒V where 1 = 1    and ID = new.NODE⠒ID    and 1 = 1; is supposed to be doing especially the 1 = 1 tests? The selec

Re: Me again with an insert trigger problem

Am 27.02.2024 um 23:20 schrieb Adrian Klaver: On 2/27/24 14:11, Thiemo Kellner wrote: It is a habit of mine to pad conditions in the where clause. This way, it is easy to comment/uncomment parts of the clause for testing purposes. Coming from Oracle, I missed that using "true&quo

select results on pg_class incomplete

Hi I am trying to access PostgreSQL meta data, possibly in a vane attempt to get size data. I use DbVis with a connection as shown in https://ibb.co/2SDzhXt . I try to get information on a regular table "umsaetze". When doing the DbVis object I can see them - https://ibb.co/WxMnY2c . If I ex

Re: select results on pg_class incomplete

Thanks for the enlightenment. A pity. I suppose, there is no working around this? Am 14.03.2024 um 18:01 schrieb Adrian Klaver: On 3/14/24 09:41, Thiemo Kellner wrote: Hi I am trying to access PostgreSQL meta data, possibly in a vane attempt to get size data. I use DbVis with a connection

Re: select results on pg_class incomplete

Am 14.03.2024 um 21:03 schrieb David Rowley: Yeah, maybe dblink and a LATERAL join might be an easy way. Something like: create extension dblink; select d.datname,c.relname from pg_database d, lateral (select * from dblink('dbname='||d.datname,$$select relname from pg_class where relname = 'p

Re: select results on pg_class incomplete

You solve a problem that no one has. Data belonging together may still be divided into schemas in a database. Thus, the metadata is also reported and archived individually per database. I am not sure, we are taking about the same problem, but would be surprised to be the only one having exper

Re: select results on pg_class incomplete

You could also create a PostgreSQL foreign server for each of the other databases, which would let you issue a query to UNION together the results of a query on all of the catalogs. This would require creating a foreign table for pg_class in the other databases. Thanks. So many possibilit

Re: select results on pg_class incomplete

https://wiki.postgresql.org/wiki/Monitoring Thanks for the URL. I am not too keen to re-invent the wheel. Although it teaches me on PostgreSQL.

Re: PostgreSQL as advanced job queuing system

Am 22.03.2024 um 14:15 schrieb Fred Habash: We developed a home-grown queue system using Postgres, but its performance was largely hindered by que tables bloating and the need to continuously vacuum them. It did not scale whatsoever. With some workarounds, we ended up designing three sets of

Re: Is this a buggy behavior?

Am 24.03.2024 um 15:54 schrieb Erik Wienhold: This is required by the SQL standard: columns of a primary key must be NOT NULL. Postgres automatically adds the missing NOT NULL constraints when defining a primary key. You can verify that with \d test1 in psql. To me, this behaviour, while cor

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause. Considering that this usage of NULL isn't even permitted by the SQL standard, that seems like a bit too much work. If I understood c

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer: the null-able constraint addition to a column is pointless because by default all columns are nullable. definition as a primary key adds the not null constraint. While this is certainly true, I do not see why the information that a not nul

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:39 schrieb Erik Wienhold: And that's also possible in Postgres with UNIQUE constraints if you're looking for that behavior. Sort of the distinction between PK and UQ.

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:35 schrieb sud: On Sun, Mar 24, 2024 at 8:47 PM Tom Lane > wrote: Do you specifically mean that 'null'  keyword is just not making any sense here in postgres. But even if that is the case , i tried inserting nothing (hoping "nothing" is "null" in

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:44 schrieb Andreas Kretschmer: postgres=# create table bla(i int null primary key); CREATE TABLE postgres=# \d bla     Table "public.bla"  Column |  Type   | Collation | Nullable | Default +-+---+--+-  i  | integer

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:15 schrieb Christophe Pettus: I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects (not even really a side-effect) of that request is that the column i

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:43 schrieb Christophe Pettus: The situation is much more like the customer saying, "I understand that the standard paint for this car is red, but I wish it painted blue instead." Not in the least. Declaring the column to be NULL is explicitly requesting the car be blue. An

Empty materialized view

Hi I have created a materialized view with "with data". And I refreshed it with "with data". The query of the mv returns records when executed outside the mv. I would appreciate help with respect to what I miss that my mv is empty. You might want to have a look at the code attached. Kind reg

Re: Empty materialized view

Am 24.03.2024 um 20:56 schrieb Erik Wienhold: Maybe you executed REFRESH in a transaction but did not commit it? While I can see the point for the refresh (but there actually is a commit), I cannot hold it valid for a create with data when the mv actually is created (confirmed by being empt

Re: Empty materialized view

Am 24.03.2024 um 21:30 schrieb Adrian Klaver: On 3/24/24 13:11, Thiemo Kellner wrote: Confirmed in the same session that created it or in a different session? Different session, not knowing what that mattered. Excerpt of the installation protocol: … ## tenth level ## Set materialised view

Re: Empty materialized view

Am 24.03.2024 um 21:50 schrieb Adrian Klaver: On 3/24/24 13:36, Thiemo Kellner wrote: It does depending on the order of viewing. Namely if you viewed the 'old' empty MV in the outside session before you dropped/created the 'new' MV and committed the changes. Someth

  1   2   >