Dear Members!
As I experienced, the functions/procedures extremely depend on parameters
(very typed).
So if I have to restructure the input parameters, I can't modify the
function, because I have to recreate the dependents too.
For example:
I have a type. If I pass this type to a function, I can't
Hello!
I have a script which can change the table owners to the database owner.
I select the tables like this:
FOR r IN SELECT tablename FROM pg_tables WHERE (schemaname = 'public')
and (tableowner <> act_dbowner)
LOOP
...
For types I found pg_type, but this contains all types.
For exa
Dear Members!
I would like to ask if the PG 10-11 versions are also affected by MAT VIEW
security problems?
They aren't listed in this page:
https://www.postgresql.org/support/security/CVE-2024-0985/
But maybe that's because PG 10 and 11 are no longer supported - and not
because they aren't af
Dear Thomas, Dear All!
Thank you for the answers, and explanations!
Thomas Kellerer ezt írta (időpont: 2023. aug. 25., P,
16:02):
>
> With Postgres 15 it should be as simple as:
>
>CREATE ROLE CREATE ROLE u_tdb_ro WITH LOGIN;
>GRANT pg_read_all_data TO u_tdb_ro;
>
> In previous versions
Dear Members!
Normally we use the "db owner" role for the connection, but this can do
everything (DDL-DML).
Somewhere they want to access a DB through a Read Only connection.
In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell".
Formerly we tried to use multiple roles with
Dear Members!
I have a table with temperature measures.
The data is coming from the PLC, but sometimes the period is "slipping", so
the values are not correctly minute based.
03:00 10
03:02 12
03:03 11
03:05 13
I have to make a virtual table which is minute based.
I thought I wo
elect.
>
> If the subselect needs to use a reference from the outer scope (i.e. the
> main query) , you can use the `lateral` modifier.
>
> Best regards.
>
>
> On 10/03/2023 08:34, Durumdara wrote:
>
> Dear Members!
>
> I use the subselects many times in my Q
ant?
> https://www.postgresql.org/docs/current/functions-window.html
>
> KR
> Mikael
> ------
> *Från:* Durumdara
> *Skickat:* den 10 mars 2023 8:12
> *Till:* Postgres General
> *Ämne:* Onfly, function generated ID for Select Query
>
> Dear Members!
Dear Members!
I use the subselects many times in my Queries to get some info (Name, etc)
from a subtable.
Sometimes I have to use it to get the last element.
select t.*,
(
select value from u join ... where ...
order by id desc limit 1
) as last_value,
It is ok, but how can I get more
Dear Members!
I'm searching for a simple solution, like this:
select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where
Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for th
Dear Members!
Today we found strange database collation names in a server (V11).
select -- datname,
distinct datcollate
from pg_database
order by datcollate --, datname;
"hu_HU.UTF-8"
"hu_HU.UTF8"
"hu_HU.utf8"
The PGAdmin also gives us these possible collations in the dialog.
Some of th
3., H, 19:42):
> On 5/23/22 10:35 AM, Durumdara wrote:
> > Hello!
> >
> > I need to log some row changes in a table with JSON format (row_to_json).
> >
> > In the Before Update I can select the row into a column with JSON format.
> > And in After Update
Hello!
I need to log some row changes in a table with JSON format (row_to_json).
In the Before Update I can select the row into a column with JSON format.
And in After Update I can log this column into an archive table.
Plus I have the prior data in the JSON_TEXT field.
It's ok.
FUNCTION Before
Hello!
Peter J. Holzer ezt írta (időpont: 2022. máj. 11., Sze,
0:44):
> On 2022-05-07 15:02:09 +0200, Durumdara wrote:
>
> > So in the new trigger we can't load them all with:
>
> How do you get a new trigger on one table but not the new columns on the
> other table? W
Szo, 16:41):
> On Saturday, May 7, 2022, Durumdara wrote:
>
>>
>>
>> So is there any syntax to not fall on missing columns?
>>
>
> No. I’d probably approach this by generically converting the NEW record
> to json and working with that. Non-existent object keys return null when
> accessed.
>
> David J.
>
Hello!
We stored the username in a temporary table which was created by the client
app.
With this technique we can log these names in triggers too.
Now we extend it with user id and later the comp name.
Because we can update the client applications slowly, some client's tables
have these one or
values(oError, oLog); <--- post log everytime
if oError > '' then
commit; <--- on error we save the logs with commit + raise an error
again
raise Exception oError;
end if;
...
Thanks for it!
dd
Tom Lane ezt írta (időpont: 2022. márc. 11., P, 16:01):
> Duru
Hello!
A critical question for me because of future planning.
In autocommit mode, when I start a simple update or select, it is one
transaction, so if something fails, the whole modification is rolled back
(there is no "half update", or "only first record updated").
What will happen with complex
Dear Members!
We have to port a native, complex Win32 code to a stored procedure. PGSQL:
V9.6-V11.
One of the problems is: how to log (to client and to server side)?
In the native code we:
1. Open a transaction.
2. Start a complex process.
3. Make local (filesystem based) log in every important
Hello!
I have a big "WITH QUERY", with many subqueries.
I have a date field, named "XDate".
In the middle I duplicated this field:
...
midqry1 as (
select coalesce(XDate , '0001-01-01'), * from prevqry
),
midqry2 as (
select ArtID, max(XDate) as Max_XDate from midqry1
where acq = True
Dear Members!
We have a server.
Because of high usage we can't log DDL-s. There are too many temporary
tables created by users, so it would slow down the server.
A database owner changed.
What can we do to get info about the change (who did it, from what IP, when
it happened) in the future?
As I
Hello!
Durumdara ezt írta (időpont: 2021. jan. 7., Cs,
10:17):
> Hello!
>
> I have a query where I can show the positive and negative future changes
> of the articles.
> For example:
>
> select art.id, art.name, art.actual_stock, art.min_stock,
> change.stock_change, c
Dear Members!
Pavel Stehule ezt írta (időpont: 2021. jan. 6.,
Sze, 12:03):
>
>
>
> it cannot work, because \ will be replaced by \\
>
> postgres=# CREATE OR REPLACE FUNCTION public.unistr(text)
> RETURNS text
> LANGUAGE plpgsql
> IMMUTABLE STRICT
> AS $function$
> declare r text;
> begin
Hello!
I have a query where I can show the positive and negative future changes of
the articles.
For example:
select art.id, art.name, art.actual_stock, art.min_stock,
change.stock_change, change.date
from change left join art on art.id = change.art_id
order by art.id, change.id
Ok, I have a lis
Dear Members!
A web developer stores JSON like strings in a text column.
With E prefix we can get the real text:
Select E'Az ad\u00f3kulcsonk\u00e9nti'
Hungarian: "Az adókulcsonkénti" (ISO-8859-2)
How to get the same result from a table column?
select WhatAFunction( ATable.JSONLikeTextColumn)
Hello!
We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.
But when I try to redirect the output, the result doesn't have timestamps.
PG 11, on Windows.
As I see the -v option isn't enough to see the starting times.
For example:
2020-11-19
Hello!
For CRL reloading we need to restart the PG In 9.6.
Now we have a new server with 11.x.
As I read it has a reload possibility without restart and dropping the
connections.
SELECT pg_reload_conf();
It's ok. But: the restart is a very good checking point.
If you did it, and after the serv
Dear Tom!
Tom Lane ezt írta (időpont: 2020. júl. 20., H, 15:38):
>
> There are -j threads in the pgbench process, and -c connections to
> the server (hence -c backend processes on the server side). Each
> of the pgbench threads is responsible for sending queries to a subset
> of the connections
allel performance tests.
Durumdara ezt írta (időpont: 2020. júl. 20., H,
15:20):
> Dear Members!
>
>
> I have a question about PGBench for Windows (9,6).
>
> I want to understand the working method of this tool for use well
> in the test series.
>
> This has more options, like
Dear Members!
I have a question about PGBench for Windows (9,6).
I want to understand the working method of this tool for use well
in the test series.
This has more options, like connections (c).
As I tried the c controls how much concurrent connections must be used in
the test. For example c
Hello!
PGSQL 9.6, Linux, SSL.
We want to change certs to new, but somehow we need to detect which old
cert is in use before the expiration.
So now they could connect with old and new too. We want to warn the clients
with old certs to update, before they will be denied.
Do we have chance to log so
Hello!
PLPGSQL allows me to write simple queries and updates without execute +
using.
F.e:
DECLARE t text; anytype text;
BEGIN
...
select nev into t from anytable where type = anytype;
...
insert into bla (id, name, type)
select id, name, anytype from bla
...
But this method is seems to be
Hello!
I want to ask how to shortening code?
Delta double precision;
UjErtek double precision;
UjDErtek double precision;
For example - but this makes an error:
Delta, UjErtek, UjDErtek double precision;
Do you know any other form?
Thank you for it!
Best regards
dd
Dear Members!
We have more than 200 databases in a server.
The PGAdmin's dashboard shows me 4500 inserts periodically.
I want to know which database(s) causes this.
Do you know any query which can show me the inserts per databases?
And I don't know it works as TPS query? So I need to make diffe
se inner transactions - so I must make client
programme to use begin/commit... (PGAdmin?).
Thanks for the prior infos!
Best regards
dd
hubert depesz lubaczewski ezt írta (időpont: 2020. jan.
30., Cs, 17:20):
> On Thu, Jan 30, 2020 at 04:51:02PM +0100, Durumdara wrote:
> > Is there
Dear Members!
I've read this article, but I need your experience in this theme.
https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.XjL3fcqYXDc
alter table tk
add colum field1 default 'MUCH';
The table tk have 200 million rows. The autovacuum is no problem, only the
long u
Hello!
I need to ask about last_autovacuum column in *pg_stat_all_tables.*
I'm not sure is this a starting time, or when the daemon finished.
I make an "accident" with changing many rows in a huge table.
I didn't want to start autovacuum with this operation.
They reported slowing, but I'm not
Dear Members!
We have PGSQL 9.6.xxx on a Linux server which heavily used.
More than 100 databases, and more than 300 active users, and it is a master
of a cluster (the data replicated on a slave).
Somewhere we have read that 9.6 will become unsupported shortly.
We need to prepare upgrade.
In Wi
. okt. 17., Cs,
17:43):
> On Thu, Oct 17, 2019 at 5:10 PM Durumdara wrote:
> > Please help me, how the PGSQL full vacuum works internally? (1., 2.
> case, or something else)
> >
>
> As far as I know a VACUUM FULL will rewrite the whole table without
> inserting (and th
Hello!
We stored some binaries in largeobjects.
Because of that the table size now 80 GB.
We deleted 80% of records (lo_unlink), and autovacuum reclaimed space for
new elements. So the table don't grow anymore, but we have to get more
space in this server.
We can delete 99% of these records, but
Dear Members!
Many times I need to type same expressions in SQL queries.
select [exprs] from anytable
where [exprs] > 0
order by [exprs]
Sometimes I solve this with subquery, or "WITH" query, because it is easier
to define value once, and use it more times...
select * from (
select [exprs] as e
Dear Members!
I have a query which I extended with an extra calculated column.
I need to list the request, and the last date when they scheduled.
Example:
select
request.*,
(
select max(s_date) as s_date from schedule
where schedule.product_id = request.product_id and sch
Hi!
A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.
The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)
For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
selec
Hello!
We have found some solutions here:
https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.W-7bFEeYWHs
https://dba.stackexchange.com/questions/132851/database-frozen-on-alter-table
Best regards
dd
Durumdara ezt írta (időpont: 2018. okt. 5., P, 14:55):
> D
Dear Members!
We have a big database somewhere with more than 150 active connection.
Sometimes we experienced a "lock" situation, the client's and programs are
halted on a point.
We investigated a little, and we recognized that this problem seems to be
appears when we modify a table (alter) on h
Dear Laurenz!
> You have to install the collation on the Windows maching, not in
PostgreSQL.
We could add languages. As I remember my coll. told me that Hungarian lang.
pack is installed in that machine.
But maybe this is different thing.
What we need to do to PGSQL see that collation? Would you
Dear Members!
One of the customer preinstalled a 9.6 PGSQL server for us.
Today we tried to create and migrate our database.
CREATE DATABASE yyy
WITH
OWNER = xxx
ENCODING = 'UTF8'
*LC_COLLATE = 'Hungarian_Hungary.1250' <*
*LC_CTYPE = 'Hungarian_Hungary.1250' <*
But
Dear Adrian!
2018-03-20 16:33 GMT+01:00 Adrian Klaver :
> On 03/20/2018 07:56 AM, Durumdara wrote:
>
>>
>> pg_dump --disable-triggers -d test -U aklaver -t disable_trigger_test -a
> -f disable_trigger_test_data.sql
>
>
> CREATE OR REPLACE FUNCTION public.t
Dear Adrian!
2018-03-20 15:47 GMT+01:00 Adrian Klaver :
>
>>
>> When it would be useful?
>>
>
> https://www.postgresql.org/docs/10/static/app-pgrestore.html
> "--disable-triggers
>
> This option is relevant only when performing a data-only restore. It
> instructs pg_restore to execute comman
Dear Members!
I saw in PGAdmin 3/4 that pg_restore have an option "disable triggers".
Because we need to move some databases in the near future I have to know
about the meaning of this option.
I wrote a table with an BEFORE UPDATE trigger:
create table tr_test
(
id integer not null primary key,
Hello!
2018-02-13 21:21 GMT+01:00 George Neuner :
> On Tue, 14 Nov 2017 12:09:31 +0100, Durumdara
> wrote:
>
>
> >*I disabled my firewall at home - the [keepalive] problem vanished!!!*
>
> What firewall are you using? Windows own firewall doesn't interfere
&g
Dear Charles!
I did search on backup SQL, and I found 4 lines. All of them needed. I
reversed them (Revoke to Grant):
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TYPES to PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TYPES to suser;
ALTER DEFAULT PRIVILEGES FOR ROLE sus
Dear Charles - Prof. Xavier? :-)
I made a restore to my local Windows PG.
Here I also see these anomalies.
4594262;0;"S";"{}"
4594262;0;"T";"{}"
4594262;0;"f";"{}"
4594262;0;"r";"{}"
In restore SQL I saw:
ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE ALL ON TABLES FROM suser;
Because of that
Hello!
2018-02-15 14:19 GMT+01:00 Charles Clavadetscher :
> What version of PostgreSQL are you using?
>
> And how did you get those first entries at all?
>
> What happens if you issue
>
> ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO
> PUBLIC;
>
> again?
>
>
> After that:
Dear Charles!
2018-02-12 10:03 GMT+01:00 Charles Clavadetscher :
> Hi
>
>
>
> *From:* Durumdara [mailto:durumd...@gmail.com]
> *Sent:* Montag, 12. Februar 2018 09:32
> *To:* Postgres General
> *Subject:* Remove default privilege from DB
>
>
>
> Hello!
>
&g
Hello!
2017-11-14 12:09 GMT+01:00 Durumdara :
>
> Windows 10, PGSQL 9.4 and 9.6 (Debian with SSL, and Windows without it -
> it doesn't matter).
>
> When I (or my boss) work(s) at home, I got connection lost errors from
> PGAdmin (3/4) or from other applications too.
Hello!
I need to remove default privileges from a Database.
After that some of them remains.
Default access privileges
Owner| Schema | Type | Access privileges
++--+---
postgres || function | =X/postgres
postgres |
Dear Members!
Thank you for the suggestions.
Yes, the sysadmin allowed incoming connections from net.
We will check your list when we are there.
Thank you again!
Best wishes
dd
Hello!
Somewhere the system administrator (who don't know the PG really) installed
a PGSQL server (10.x) with a database.
He couldn't manage the server well.
Yesterday my colleague saw 21 databases in this server with random names.
He checked it with built in PGAdmin IV.
Today we checked it again
Dear Peter and Francisco!
Firstly: sorry for name change. I might read too many about Totti or I was
listening too many "Vamos a la playa" spanitaliano disco... :-) :-) :-)
To better understand my problem I write about the background.
We used different database engine "before" PG, and more codes
rs we are using here in daily
work, and they are in very similar order.
I never thought that it can't handle normal characters in 7 bit range...
For these numbers I can use C collation, it's ok.
Thank you!
dd
2018-01-11 11:11 GMT+01:00 Francisco Olarte :
> On Thu, Jan 11, 2018 at
x27;, '/', '') > replace('180/2010', '/', ''),
replace('18/0212', '/', '') < replace('180/2010', '/', '')
I tried to simulate this in LibreOffice:
Normal Removed As Space As 0
18
Dear Members!
Today one of hour clients reported an error.
She made a report filtered with string range, and she got wrong documents.
I checked it.
She filtered by a document nr (varchar field -> character varying(15)).
The range was: '18/0113', and '18/0212'.
Q.Close;
Q.SQL.Text := '
dlock_timeout... :-) :-) :-)
Thanks
dd
2018-01-02 15:02 GMT+01:00 Jeff Janes :
> On Tue, Jan 2, 2018 at 3:22 AM, Durumdara wrote:
>
>> Dear Members!
>>
>> I have to ask something that not clear for me from description, and I
>> can't simulate it.
>>
>>
Hello!
Read Committed.
I extend the example: the concurrent connections are in transactions.
begin
select ... for update;
end;
Regards
dd
2018-01-02 12:31 GMT+01:00 Rakesh Kumar :
>
> Shouldn't isolation level also matter ? What is the isolation level you
> are using ?
>
Dear Members!
I have to ask something that not clear for me from description, and I can't
simulate it.
Is "select for update" atomic (as transactions) or it isn't?
I want to avoid the deadlocks.
If it's atomic, then I don't need to worry about concurrent locks.
But I think it's not.
This is an
Hello!
Somewhere the users made mistakes on prices (stock).
I need to search for big differences between values.
For example:
20
21
21,5
30
28
..
46392 <-
46392 <-
But it could be:
42300
43100
44000
43800
65000 <-
42100
Human eye could locate these values, but there we need to check 30.000
ar
Dear David!
To know - your solution is working well!
*EXECUTE format('SET ROLE %I', variablename); *
Thank you!
bw
dd
but in PGAdmin this script
would be good.
Thank you!
dd
2017-11-22 14:55 GMT+01:00 Durumdara :
> Hello!
>
> May you know the way how to set role dynamically.
>
> DO
> $$
> DECLARE act_dbowner varchar(100);
> BEGIN
>
> SELECT u.usename into act_dbowner
Hello!
May you know the way how to set role dynamically.
DO
$$
DECLARE act_dbowner varchar(100);
BEGIN
SELECT u.usename into act_dbowner FROM pg_database d
JOIN pg_user u ON (d.datdba = u.usesysid)
WHERE d.datname = (SELECT current_database());
raise notice 'DB owner: %', act_dbowner;
*set role
70 matches
Mail list logo
