https://geoobserver.wordpress.com/2021/07/09/happy-birthday-postgresql/
mikeE., #geoObserver
On Thursday, July 8, 2021, Wiwwo Staff wrote:
> On Thu, 8 Jul 2021 at 21:42, Alban Hertroys com> wrote:
>
>> On 2021-07-08 13:30, Ron wrote:
>> > Thus, the bigTable PK must be on id, columnX, (No, I don't like it
>> > either.)
>>
>> That's not entirely true. You can keep the PK on id if you addi
On Thu, 8 Jul 2021 at 21:42, Alban Hertroys
wrote:
> On 2021-07-08 13:30, Ron wrote:
> > Thus, the bigTable PK must be on id, columnX, (No, I don't like it
> > either.)
>
> That's not entirely true. You can keep the PK on id if you additionally
> create a unique constraint on (id, columnX).
>
Uh
> Note that PostgreSQL 9.2 has been end of life for almost 5 years by now. If I
> were you I'd be a *lot* more worried about that than I would be about Bucardo.
I'm not saying Bucardo is good or bad, nor saying that I am not worried about a
production system having PG 9.2. It's quite the opposit
Hello,
I am curious.
NVL, DECODE and SELECT FROM dual are Oracle methods and these appear in
your code.
How did you make these work in Postgres?
Cheers,
Rob
On Thu, Jul 8, 2021 at 1:29 PM Tom Lane wrote:
> So the problem is not lack of a server feature, it's persuading pg_dump
> to emit something other than what it does now.
>
So basically a different variation on the let someone else who feels hot
enough about it and is able to code in C figure out
Hi all,
If I'm seeing instances like this in our logs, what should I look for:
2021-07-06 22:15:34.702
UTC,"bcaas_api","bcaas",8124,"10.122.45.33:46386",60e4d5e6.1fbc,222,"COMMIT",2021-07-06
22:15:02 UTC,37/0,0,LOG,0,"duration: 7128.250 ms",""
2021-07-06 22:15:34.702
UTC,"bcaas_api","
"David G. Johnston" writes:
> IIUC, functions can force a search_path even during dump/restore by being
> created with one specified as part of the create function command. Since
> the issue is with stored objects moreso than queries generically is it
> feasible to approach the view solution by a
On Thu, Jul 8, 2021 at 1:09 PM Tom Lane wrote:
>
> This isn't the only SQL syntax that has implicit operators; CASE is
> another example, and I think there are more. We've discussed inventing
> non-SQL-spec syntax that can cope with explicitly writing a qualified
> operator name in all these cas
On Thu, Jul 8, 2021 at 1:09 PM Tom Lane wrote:
> This isn't the only SQL syntax that has implicit operators; CASE is
> another example, and I think there are more. We've discussed inventing
> non-SQL-spec syntax that can cope with explicitly writing a qualified
> operator name in all these cases
"David G. Johnston" writes:
> I'll admit these have been infrequent since resolving CVE 2018-1058, but I
> still disagree with the decision to not give the DBA an option on whether
> to leave public in their search_path during a pg_dump and pg_restore.
Yeah, I was never for that decision either.
On Thu, Jul 8, 2021 at 1:09 PM Tom Lane wrote:
>
> I don't think there's any good solution right now.
>
For joins it is generally easy enough to resort to the ON clause instead of
USING so of the various places there are problems this is probably the
least.
I'll admit these have been infrequent
The docs are pretty clear that all constraints must have the partition key
as part of the key, and PK is most certainly a constraint.
I welcome a counter-example.
On 7/8/21 10:23 AM, Alban Hertroys wrote:
On 2021-07-08 13:30, Ron wrote:
> Thus, the bigTable PK must be on id, columnX, (No, I do
"Christopher Causer" writes:
> I have a large database whose schema I have recently changed. Since then I
> cannot restore its dump using the normal `psql -1 -f ...` route. Running
> `psql -1 -f ...` gives the error shown in the subject, yet pasting the
> failing view in the terminal afterward
On Thu, Jul 8, 2021 at 12:51 PM Christopher Causer
wrote:
>
> ```
> SELECT pg_catalog.set_config('search_path', '', false);
> ```
>
The data types you are using exist in the public schema. I must assume the
associated equality operator also exists in the public schema. So, when
the search_path
On 7/8/21 12:09 PM, Emiliano Saenz wrote:
I can see that you say but the database behavior is like the block is
more general than one tuple.
It is difficult to get a pg_lock snapshot to determine some access
exclusive locks on some tables.
Monitoring the database (by Zabbix), when this type of b
Hello,
I originally posted this as a StackOverflow question[1], but one of the
responses there suggested I may get further help here on pgsql-general. The
question is perhaps a little more fleshed out than what follows, but I hope
this email is self-contained.
I have a large database whose sch
I can see that you say but the database behavior is like the block is more
general than one tuple.
It is difficult to get a pg_lock snapshot to determine some access
exclusive locks on some tables.
Monitoring the database (by Zabbix), when this type of block appears
(AccessExclusiveLock) the CPU co
On 2021-07-08 13:30, Ron wrote:> Thus, the bigTable PK must be on id, columnX, (No, I don't like it > either.)That's not entirely true. You can keep the PK on id if you additionally create a unique constraint on (id, columnX).That way, you can at least be certain that the uniqueness of the PK remai
Hi Lewis,
I am new to postgres.
Could you tell me how should I put the result on an analyzed temp table at
least ?
Please suggest.
Regards.
On Thursday, July 8, 2021, Michael Lewis wrote:
> ((current_setting('env.groupid'::text))::integer)::numeric
>
> If you pass this value in directl
On Thu, Jul 08, 2021 at 02:35:33PM -0300, Emiliano Saenz wrote:
> Attach the files.
The pg_locks file doesn't show any access exclusive locks on any table?
=$ awk -F, 'NR==1 || $13 == "AccessExclusiveLock"' pg_locks.csv
Locktype,Database,Relation,Page,Tuple,Virtualxid,Transactionid,Classid,Objid
Big big variability on partitioned column, which is filtered (where
condition) in (almost) all queries.
On Thu, 8 Jul 2021 at 14:23, Michael Lewis wrote:
> Why are you using hash partitioning?
>
zickz...@quantentunnel.de writes:
> After you're answer I did a few investigations. If I insert data with a
> single insert, everything is working like expected
> INSERT INTO public.ab VALUES (1, DEFAULT);
> this changes if I do multiple inserts in one statement:
> INSERT INTO public.ab VALUES (1,
Hi David,
thanks for you're quick answer.
I'am using postgres 12 and also tested with 13. Both inside the official
(debian based) docker hub hosted container.
After you're answer I did a few investigations. If I insert data with a single
insert, everything is working like expected
INSERT INTO
((current_setting('env.groupid'::text))::integer)::numeric
If you pass this value in directly as part of the query string, how does it
perform? It seems like calling the function to get this value may be
creating a planning problem with the value unknown at plan time. If you
were able to put the r
Why are you using hash partitioning?
On 7/8/21 3:42 AM, Wiwwo Staff wrote:
Hi!
I have a big table bigTable which I partitioned by hash on field columnX,
by creating bigTable_0, bigTable_1etc.
Since I need a PK on bigTable.id, and table is not partitioned by id, and
columnX is not unique, i added PK on bigTable_0.id, bigTable_1.id
Hi,
I have one query like below :
SELECT
m.iMemberId "memberId",
m.cFirstName "firstName",
m.cLastName "lastName",
m.cFirstName || ' '
Hi!
I have a big table bigTable which I partitioned by hash on field columnX,
by creating bigTable_0, bigTable_1 etc.
Since I need a PK on bigTable.id, and table is not partitioned by id, and
columnX is not unique, i added PK on bigTable_0.id, bigTable_1.id etc. So
far, so good.
Now I have another
I have a setup with the following:
One (1) "main" site
Ten (10) "regional" sites
"main" site is a publisher to each of the regional sites (for 20 tables).
"main" site is also a subscriber from each of the 10 regional sites (5 tables).
In short: The main site has 1 publication and 10 subscription
30 matches
Mail list logo
