Hi,
I have postgres 9.6 cluster running on centos 7 machine.
when I set search_path to any user made schema with below command
[enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb
edb=# \c test
set search_path to college;
and after listing the tables with command \dt, we should get list of
tables of
Thanks for the reply
> VIEWs operate as if they were "SECURITY DEFINER".
My concern is that the view is not acting as a security barrier underneath
which all access is evaluated using the view owner context; in some
circumstances (when the view calls a function) the access is evaluated using
t
Sure. I will try that.
On Tue, Feb 23, 2021 at 4:42 PM Adrian Klaver
wrote:
> On 2/23/21 4:25 PM, Santosh Udupi wrote:
> > Yes, this is what we have been doing now:- Backup using pg_dump, create
> > the new database at the destination, manually create the tables which
> > give problems, and the
On 2/23/21 4:25 PM, Santosh Udupi wrote:
Yes, this is what we have been doing now:- Backup using pg_dump, create
the new database at the destination, manually create the tables which
give problems, and then do the pg_restore. Another solution for us is to
Well that introduces another error,
Yes, this is what we have been doing now:- Backup using pg_dump, create
the new database at the destination, manually create the tables which give
problems, and then do the pg_restore. Another solution for us is to backup
using pg_dump that comes with pgAdmin (Windows), rsync it to the
destination
On 2/23/21 12:57 PM, Santosh Udupi wrote:
So that it makes it a lot easier for the application logic just to
collect json fields and update in one column "info" instead of including
multiple columns in the insert/update statements.
I doubt it, but then again this why I don't answer Postgres/JS
On 2021-02-23, Tom Lane wrote:
> Hmm ... did you remember to set the oprcanhash property on the equality
> operator?
Aaah! That was it, I had totally missed the HASHES and MERGES options to
CREATE OPERATOR. It works perfectly now that I set those.
Do those options make sense for other operators b
On 2021-02-23, Tom Lane wrote:
> Have you tried "enable_seqscan = off" (and maybe also disable merge
> and hash joins) to see if you can force choice of that plan?
No luck. It uses the index now, but seemingly only to loop over it. The
integer version uses a HashAggregate, I must have missed some
So that it makes it a lot easier for the application logic just to collect
json fields and update in one column "info" instead of including multiple
columns in the insert/update statements.
On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver
wrote:
> On 2/23/21 12:15 PM, Santosh Udupi wrote:
> > Here
On 2/23/21 12:15 PM, Santosh Udupi wrote:
Here is my table structure. I will try to get the pg_dump output for
this table in both the versions.
create table tbl_main(
item_id int GENERATED ALWAYS AS IDENTITY,
-
operating_offices i
Here is my table structure. I will try to get the pg_dump output for this
table in both the versions.
create table tbl_main(
item_id int GENERATED ALWAYS AS IDENTITY,
-
operating_offices int [] GENERATED ALWAYS AS (
nullif(array[(info->>'o')::in
(forgot to cc the list, sorry for the duplicate, Tom!)
On 2021-02-23, Tom Lane wrote:
> Ayo writes:
> > I have a database where I converted an integer primary key column to a
> > custom base type that pretty much amounts to a wrapper around an
> > integer, and now some queries are resulting in mu
Santosh Udupi writes:
> Both are different versions
> The following works:
> Version: pg_dump (pgAdmin Windows) version:13.1
> The following does not work: (Does not populate the generated column
> values)
> Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04) version 13.2
Hmm ... well, that wo
Ayo writes:
> Aaah! That was it, I had totally missed the HASHES and MERGES options to
> CREATE OPERATOR. It works perfectly now that I set those.
Cool.
> Do those options make sense for other operators besides equality, too?
No, they just flag that the operator is equality in some hash or btre
Both are different versions
The following works:
Version: pg_dump (pgAdmin Windows) version:13.1
Method: Backup using Windows connecting to the remote host:
Command: pg_dump -Fc -p 5432 -h -d mydb > mydb.backup1 -U
postgres
Restore locally on Ubuntu : pg_restore -d mydb-restore mydb.backu
Hi,
The short version:
I have a database where I converted an integer primary key column to a
custom base type that pretty much amounts to a wrapper around an
integer, and now some queries are resulting in much slower query plans.
Does Postgres have special optimizations for integers that are not
Ayo writes:
> No luck. It uses the index now, but seemingly only to loop over it. The
> integer version uses a HashAggregate, I must have missed something in my
> implementation to make the planner avoid that node. Does it have any special
> type requirements, other than the hash operator class?
Santosh Udupi writes:
> Right. pg_dump under the pgAdmin runtime folder works perfectly. pg_dump
> in postgres13 (ubuntu) does not work. Exact same syntax.
So, are these identical pg_dump versions? We did fix some things
in this area in 13.2.
regards, tom lane
Ayo writes:
> On 2021-02-23, Tom Lane wrote:
>> The slow query isn't using the chars_pkey1 index, which makes one
>> wonder if you have a corresponding index in the custom-type case,
> The index exists and is usable in the custom-type case:
Hmm. The next most likely theory seems to be something
Right. pg_dump under the pgAdmin runtime folder works perfectly. pg_dump
in postgres13 (ubuntu) does not work. Exact same syntax.
When I try to restore, the backup that was taken using pgAdmin's version
restores properly but the one taken using postgres13's pg_dump, restores
the database but does
Luka Zivkovic writes:
> Yeah, i was looking i to that, and it looks like i just didn't implement
> it correctly. I'm just wondering because I'm using it for jsonb type, can i
> just use already implemented support functions for jsonb? I'm just not too
> sure how those functions work
The support f
On 2/23/21 7:39 AM, Santosh Udupi wrote:
Got it. Must be the version difference. I run pgAdmin on Windows PC but
direct pg_dump on Ubuntu 20.04.
The OS does not really make a difference it is the pg_dump/restore
versions and the Postgres server(s) versions that are important.
On Tue, Feb 2
On 2/22/21 10:32 PM, David Wheeler wrote:
> I’m seeing some inconsistency with how permissions are enforced within views.
> In
> particular, if the view accesses a table directly, then the table is
> accessible,
> however if the view uses a function to access the table then permission is
> denie
Thank you for your response!
Yeah, i was looking i to that, and it looks like i just didn't implement
it correctly. I'm just wondering because I'm using it for jsonb type, can i
just use already implemented support functions for jsonb? I'm just not too
sure how those functions work
Thanks,
Luka Ž
Got it. Must be the version difference. I run pgAdmin on Windows PC but
direct pg_dump on Ubuntu 20.04.
On Tue, Feb 23, 2021 at 7:27 AM Adrian Klaver
wrote:
> On 2/23/21 6:36 AM, Santosh Udupi wrote:
> > The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my
> > mistake).
> >
> >
Luka Zivkovic writes:
> I am having problems applying gin indexing to my new operator class for
> type jsonb.
> I created an operator that can query(jsonb column) keys and dates where
> dates are more than, less than, equal and not equal to the one provided
> inside another jsonb.
> Because I want
On 2/23/21 6:36 AM, Santosh Udupi wrote:
The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my
mistake).
I didn't provide the -h -p -U since I use the super user account to
restore (I will try adding them). The restore had always worked until I
altered the table in the source
Ayo writes:
> I have a database where I converted an integer primary key column to a
> custom base type that pretty much amounts to a wrapper around an
> integer, and now some queries are resulting in much slower query plans.
> Does Postgres have special optimizations for integers that are not
> a
Hi,
On Thu, 2021-02-18 at 16:23 +, Haas, Scott wrote:
> RHEL 7.9
> PostgreSQL 12 (12.5.1 to 12.6.1)
>
> I am updating RPMs for postgres and was interested to know whether
> this is indeed expected behavior for the rpm update:
>
> When the "yum update" is executed, postgresql rpms are update
The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my
mistake).
I didn't provide the -h -p -U since I use the super user account to restore
(I will try adding them). The restore had always worked until I altered the
table in the source database.
After I added the column, the res
It's standard procedure for package managers to stop daemons before update
and then start them back up afterward.
On 2/23/21 8:22 AM, Haas, Scott wrote:
Tickling this thread to see if I can get a confirmation of the described
behavior for rpm update via yum.
I do not want to cross-post (but
Hello all!
I am having problems applying gin indexing to my new operator class for
type jsonb.
I created an operator that can query(jsonb column) keys and dates where
dates are more than, less than, equal and not equal to the one provided
inside another jsonb.
Because I want to make it use index s
Tickling this thread to see if I can get a confirmation of the described
behavior for rpm update via yum.
I do not want to cross-post (but if this is not the correct forum, please let
me know).
Thanks,
Scott
From: Haas, Scott
Sent: Thursday, February 18, 2021
On Mon, Feb 22, 2021 at 3:42 PM Luca Ferrari wrote:
> If, instead, I do pg_wal_replay_resume(), the server is promoted (of
> course not at the PITR I want).
> Am I missing something?
Apparently I was missing caffeine and confused the usage of the functions.
pg_wal_replay_resume was what I was loo
Greetings,
i have tested both options ANY with string + parsing and simple array - and
there are cases when execution time 100+ times worse than IN
On Tue, Feb 23, 2021 at 12:23 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Monday, February 22, 2021, Oleksandr Voytsekhovskyy
>
Hi
út 23. 2. 2021 v 10:36 odesílatel Alexander Voytsekhovskyy <
young.in...@gmail.com> napsal:
> Thanks for suggestion
>
> with tmp tables there are another issue - there are already 5-6 tables and
> 2-3 IN filters. If i will replace them with tmp tables it may hit query
> planner limits and it w
Thanks for suggestion
with tmp tables there are another issue - there are already 5-6 tables and
2-3 IN filters. If i will replace them with tmp tables it may hit query
planner limits and it will become to produce terrible query plans, for
example when genetic query optimizer starts
On Tue, Feb 2
Greetings,
Didn’t get your ideas with materialized CTE or a subquery with OFFSET 0
Could you please show simple example?
> 23 февр. 2021 г., в 04:33, Michael Lewis написал(а):
>
> Insert the values into a TEMPORARY TABLE, then join that to your main table?
>
> In my experience, this is very
On 2021-02-22 5:43 p.m., Tom Lane wrote:
> Michael Brown writes:
>> * is there a knob missing we can configure?
>
> No. The trouble with sync() is that per POSIX, it only schedules the
> writes; there's no way to tell when the work has been done. I see
> that Linux offers stronger promises in t
Hi all
I’m seeing some inconsistency with how permissions are enforced within views.
In particular, if the view accesses a table directly, then the table is
accessible, however if the view uses a function to access the table then
permission is denied.
Here’s a demonstration (from pg13.0)
crea
40 matches
Mail list logo
