Re: Cloning schemas

2018-07-09 Thread Łukasz Jarych 
Hi Melvin,

i am trying to run postgresql 10 cloning schema function but still i am
getting error...

[image: image.png]

Error: Error in syntax near "SYSTEM"
Context: Function PL/pgSQL, row 212 in EXECUTE

What is happening?

Best,
Jacek


sob., 7 lip 2018 o 22:20 Melvin Davidson  napisał(a):

>
>
> 2018-07-07 4:32 GMT-04:00 DiasCosta :
>
>> Hi Melvin,
>>
>> Thank you.
>>
>> Dias Costa
>>
>> On 04-07-2018 23:38, Melvin Davidson wrote:
>>
>>
>>
>> On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta 
>> wrote:
>>
>>> Hi Melvin,
>>>
>>> I'm new to clone_schema.
>>> Can I use it on PostgreSQL 9.6?
>>>
>>> TIA
>>> DCostaployment by invitation only!
>>>
>>
>> > Can I use it on PostgreSQL 9.6?
>> Yes, but because the developer(s) once again monkeyed with the system
>> catalogs, there are now
>> two versions. One for 10 and one for 9.6 and below. I've attached the 9.6
>> version for you.
>>
>>
>>
>>
>> --
>> J. M. Dias Costa
>> Telef. 214026948 Telem. 939307421
>>
>> Se divulgar esta mensagem por terceiros, por favor:
>> 1. Apague o meu endereço de correio electrónico e o meu nome.
>> 2. Apague também os endereços dos seus amigos antes de distribuir.
>> 3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
>> Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
>> "banners" e contribuirá para manter a privacidade de todos e cada um.
>> Obrigado.
>>
>> Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que 
>> não respeitem o
>> malfadado acordo ortográfico.
>>
>>
> You are welcome Dias!
> Good to have positive feedback.
>
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

How to set array element to null value

2018-07-09 Thread Brahmam Eswar 
I'm trying to reset array element to null. but 3rd line of below snippet is
giving the compilation error.


FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP
IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN
X[indx_1].REFERENCE_VALUE:='';
END IF;
END LOOP;

-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: How to set array element to null value

2018-07-09 Thread Pavel Stehule 
2018-07-09 11:58 GMT+02:00 Brahmam Eswar :

> I'm trying to reset array element to null. but 3rd line of below snippet
> is giving the compilation error.
>
>
> FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP
> IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN
> X[indx_1].REFERENCE_VALUE:='';
> END IF;
> END LOOP;
>
>
a) plpgsql doesn't support complex expressions on left side of assign
command, b) '' is not NULL in PostgreSQL

you can write your code some like

DECLARE r RECORD;
BEGIN
  FOR i IN array_lower(x, 1) .. array_upper(x, 1)
  LOOP
r := x[i];
IF r.reference_value = 'ABC' THEN
  r.reference_value := NULL;
  x[i] := r;
END IF;
  END LOOP;
END;

Regards

Pavel




> --
> Thanks & Regards,
> Brahmeswara Rao J.
>

Re: How to set array element to null value

2018-07-09 Thread Thomas Kellerer 
Brahmam Eswar schrieb am 09.07.2018 um 11:58:
> I'm trying to reset array element to null. but 3rd line of below snippet is 
> giving the compilation error.
> 
> 
> FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP
> IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN
> X[indx_1].REFERENCE_VALUE:='';
> END IF;
> END LOOP;

What data type is X exactly? It looks like a composite record type. 

(Also: an empty string '' is not the same as NULL)

Create event triger

2018-07-09 Thread Łukasz Jarych 
Hi,

i have small database and i am tracking changes using trigger:

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig

FOR EACH ROW EXECUTE PROCEDURE change_trigger();

It is possible to create general trigger for all tables?
Like event trigger?

It would be very helpful for me.
Now i have to set up this trigger on each table.

Best,
Jacek

Create DDL trigger to catch which column was altered

2018-07-09 Thread Łukasz Jarych 
Hi Guys,

i am using sqls like below to track ddl changes:

CREATE TABLE track_ddl
> (
>   event text,
>   command text,
>   ddl_time timestamptz,
>   usr text
> );
> CREATE OR REPLACE FUNCTION track_ddl_function()
> RETURNS event_trigger
> AS
> $$
> BEGIN
>   INSERT INTO track_ddl values(tg_tag, tg_event, now(), session_user);
>   RAISE NOTICE 'DDL logged';
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER;
>


> CREATE EVENT TRIGGER track_ddl_event ON ddl_command_start
> WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE')
> EXECUTE PROCEDURE track_ddl_function();
> CREATE TABLE event_check(i int);
> SELECT * FROM track_ddl;


And and drop table is ok. But when i am altering i would like to know new
vales and old values like when i am catching DML changes:

CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS $$
>
>
>> BEGIN
>
>
>> IF  TG_OP = 'INSERT'
>
>
>> THEN
>
>
>> INSERT INTO logging.t_history (tabname,
>> schemaname, operation, who, new_val)
>
>
>> VALUES (TG_RELNAME, TG_TABLE_SCHEMA,
>> TG_OP, current_user, row_to_json(NEW));
>
>
>> RETURN NEW;
>
>
>> ELSIF   TG_OP = 'UPDATE'
>
>
>> THEN
>
>
>> INSERT INTO logging.t_history (tabname,
>> schemaname, operation, who, new_val, old_val)
>
>
>> VALUES (TG_RELNAME, TG_TABLE_SCHEMA,
>> TG_OP, current_user,
>
>
>> row_to_json(NEW),
>> row_to_json(OLD));
>
>
>> RETURN NEW;
>
>
>> ELSIF   TG_OP = 'DELETE'
>
>
>> THEN
>
>
>> INSERT INTO logging.t_history (tabname,
>> schemaname, operation, who, old_val)
>
>
>> VALUES (TG_RELNAME, TG_TABLE_SCHEMA,
>> TG_OP, current_user, row_to_json(OLD));
>
>
>> RETURN OLD;
>
>
>> END IF;
>
>
>> END;
>
>
>> $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
>
>
It is possible?
Or write function which will tell me all new values in new columns?

I was trying to change sqls like here:

CREATE TABLE track_ddl
> (
>   event text,
>   command text,
>   ddl_time timestamptz,
>   usr json
> );
> CREATE OR REPLACE FUNCTION track_ddl_function()
> RETURNS event_trigger
> AS
> $$
> BEGIN
>   INSERT INTO track_ddl values(tg_tag, tg_event, now(), row_to_json(NEW));
>   RAISE NOTICE 'DDL logged';
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER;


but this is not working.

Please help,
Jacek

Re: How to set array element to null value

2018-07-09 Thread David Fetter 
On Mon, Jul 09, 2018 at 03:28:45PM +0530, Brahmam Eswar wrote:
> I'm trying to reset array element to null.

You can do this in SQL as follows:

SELECT ARRAY(
SELECT CASE e WHEN 'ABC' THEN NULL ELSE e
FROM UNNEST(x) _(e)
)

This should really be going to pgsql-general because to is about how
to use PostgreSQL, not how to change PostgreSQL.

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Cloning schemas

2018-07-09 Thread Melvin Davidson 
On Mon, Jul 9, 2018 at 5:14 AM, Łukasz Jarych  wrote:

> Hi Melvin,
>
> i am trying to run postgresql 10 cloning schema function but still i am
> getting error...
>
> [image: image.png]
>
> Error: Error in syntax near "SYSTEM"
> Context: Function PL/pgSQL, row 212 in EXECUTE
>
> What is happening?
>
> Best,
> Jacek
>
> > Error: Error in syntax near "SYSTEM"

Jacek,
I have changed the code from OVERRIDING SYSTEM VALUE to OVERRIDING USER
VALUE
and attached the revised version below.

If that does not fix the problem, then I will need you to do a
pgdump -F p -t public.t_cpuinfo

and send the output to me.

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2018-07-03 by Melvin Davidson
--  Added ' OVERRIDING USER VALUE' for INSERT records
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  seq_oid  oid;
  func_oid oid;
  owner_id oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  seq_name name;
  sq_type  oid;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
--  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_typname   name;
  seq_cycled   text;
  seq_ownername;  
--  sq_cycledchar(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
   
  IF NOT FOUND
THEN 
  RAISE NOTICE 'source schema % does not exist!', source_schema;
  RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
   
  IF FOUND
THEN 
  RAISE NOTICE 'dest schema % already exists!', dest_schema;
  RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
   
  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
 AND objsubid = 0;

  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

  SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
  SELECT current_schemas(TRUE) INTO v_path;
  RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
 SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)

  LOOP

SELECT oid, relowner into seq_oid, owner_id
  FROM pg_class 
 WHERE relname = quote_ident(object)
   AND relkind = 'S';

SELECT seqtypid,
   seqstart,
   seqincrement,
   seqmax,
   seqmin,
   seqcache,
   seqcycle
  INTO sq_type, 
   sq_start_value,
   sq_increment_by,
   sq_max_value,
   sq_min_value,
   sq_cache_value,
   sq_is_cycled
  FROM pg_sequence
 WHERE seqrelid = seq_oid;
 
SELECT typname INTO sq_typname
  FROM pg_type 
 WHERE oid = sq_type;

SELECT rolname INTO seq_owner
  FROM pg_authid WHERE oid = owner_id;

--EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
--srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

IF sq_is_cycled 
  THEN 
seq_cycled = ' CYCLE'; 
  ELSE 
seq_cycled = ' NO CYCLE';
END IF;

EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
 || ' AS ' || sq_typname
 || ' INCREMENT ' ||  sq_increment_by
 || ' MINVALUE ' || sq_min_value
 || ' MAXVALUE ' || sq_max_value
 || ' START WITH ' |

Re: Cloning schemas

2018-07-09 Thread DiasCosta 

Hi Melvin,

Trying run 9.6 clone_schema on a different schema and I get the 
following error:


NOTICE:  search path = {public,pg_catalog}
CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 79 at RAISE
ERROR:  relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does 
not exist
CONTEXT:  SQL statement "COMMENT ON INDEX 
bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante unicidade do 
Cod_Operador_AML';"

PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
** Error **

ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does 
not exist

SQL state: 42P01
Context: SQL statement "COMMENT ON INDEX 
bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante unicidade do 
Cod_Operador_AML';"

PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE


Can you help me, please?
Thanks in advance
Dias Costa




On 07-07-2018 09:32, DiasCosta wrote:

Hi Melvin,

Thank you.

Dias Costa

On 04-07-2018 23:38, Melvin Davidson wrote:



On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > wrote:


Hi Melvin,

I'm new to clone_schema.
Can I use it on PostgreSQL 9.6?

TIA
DCostaployment by invitation only!


> Can I use it on PostgreSQL 9.6?

Yes, but because the developer(s) once again monkeyed with the system 
catalogs, there are now
two versions. One for 10 and one for 9.6 and below. I've attached the 
9.6 version for you.




--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não 
respeitem o
malfadado acordo ortográfico.



--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não 
respeitem o
malfadado acordo ortográfico.

Re: Cloning schemas

2018-07-09 Thread Melvin Davidson 
On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta  wrote:

> Hi Melvin,
>
> Trying run 9.6 clone_schema on a different schema and I get the following
> error:
>
> NOTICE:  search path = {public,pg_catalog}
> CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 79 at
> RAISE
> ERROR:  relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does
> not exist
> CONTEXT:  SQL statement "COMMENT ON INDEX 
> bilhetica_logic_schema.idx_unq_cod_operador_aml
> IS 'garante unicidade do Cod_Operador_AML';"
> PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
> ** Error **
>
> ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does
> not exist
> SQL state: 42P01
> Context: SQL statement "COMMENT ON INDEX 
> bilhetica_logic_schema.idx_unq_cod_operador_aml
> IS 'garante unicidade do Cod_Operador_AML';"
> PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
>
>
> Can you help me, please?
> Thanks in advance
> Dias Costa
>
>
Dias
> NOTICE:  search path = {public,pg_catalog}
>ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does not
exist

This is not related to the clone_schema function. It looks like you may
have corruption in your syste catalogs,
Try reindexing your system_catalogs.

REINDEX VERBOSE SYSTEM  ;



-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

2018-07-09 Thread Peter Geoghegan 
On Thu, Jun 28, 2018 at 8:02 AM, Andres Freund  wrote:
> Peter, looks like you might be involved specifically.

Seems that way.

> This however seems wrong.  Cleary the relation's index list is out of
> date.
>
> I believe this happens because there's currently no relcache
> invalidation registered for the main relation, until *after* the index
> is built. Normally it'd be the CacheInvalidateRelcacheByTuple(tuple) in
> index_update_stats(), which is called at the bottom of index_build().
> But we never get there, because the earlier error.  That's bad, because
> any relcache entry built *after* the CommandCounterIncrement() in
> CommandCounterIncrement() will now be outdated.
>
> In the olden days we most of the time didn't build a relcache entry
> until after the index was built - but plan_create_index_workers() now
> does. I'm suspect there's other ways to trigger that earlier, too.

Note that there is a kludge within plan_create_index_workers() that
has us treat the heap relation as an inheritance parent, just to get a
RelOptInfo for the heap relation without running into similar trouble
with the index in get_relation_info(). Perhaps there's an argument to
be made for refactoring plan_create_index_workers() as a fix for this.

> Putting in a CacheInvalidateRelcache(heapRelation); before the CCI in
> index_create() indeed makes the "borked relcache" problem go away.
>
>
> I wonder why we don't just generally trigger invalidations to an
> indexes' "owning" relation in CacheInvalidateHeapTuple()?

I don't know, but that seems like a good question.

-- 
Peter Geoghegan

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

2018-07-09 Thread Andres Freund 
Hi,

On 2018-07-09 09:59:58 -0700, Peter Geoghegan wrote:
> On Thu, Jun 28, 2018 at 8:02 AM, Andres Freund  wrote:
> > I believe this happens because there's currently no relcache
> > invalidation registered for the main relation, until *after* the index
> > is built. Normally it'd be the CacheInvalidateRelcacheByTuple(tuple) in
> > index_update_stats(), which is called at the bottom of index_build().
> > But we never get there, because the earlier error.  That's bad, because
> > any relcache entry built *after* the CommandCounterIncrement() in
> > CommandCounterIncrement() will now be outdated.
> >
> > In the olden days we most of the time didn't build a relcache entry
> > until after the index was built - but plan_create_index_workers() now
> > does. I'm suspect there's other ways to trigger that earlier, too.
> 
> Note that there is a kludge within plan_create_index_workers() that
> has us treat the heap relation as an inheritance parent, just to get a
> RelOptInfo for the heap relation without running into similar trouble
> with the index in get_relation_info(). Perhaps there's an argument to
> be made for refactoring plan_create_index_workers() as a fix for this.

Maybe I'm missing something, but what has this got to do with the issue
at hand?


> > Putting in a CacheInvalidateRelcache(heapRelation); before the CCI in
> > index_create() indeed makes the "borked relcache" problem go away.
> >
> >
> > I wonder why we don't just generally trigger invalidations to an
> > indexes' "owning" relation in CacheInvalidateHeapTuple()?
> 
> I don't know, but that seems like a good question.

I assume we'll have to backpatch this issue, so I think it'd probably a
good idea to put a specific CacheInvalidateHeapTuple() in there
explicitly in the back branches, and do the larger fix in 12. ISTM
there's some risks that it'd cause issues.  Will you tackle this?

Greetings,

Andres Freund

Re: Cloning schemas

2018-07-09 Thread Adrian Klaver 

On 07/09/2018 09:49 AM, Melvin Davidson wrote:



On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta > wrote:


Hi Melvin,

Trying run 9.6 clone_schema on a different schema and I get the
following error:

NOTICE:  search path = {public,pg_catalog}
CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 79
at RAISE
ERROR:  relation "bilhetica_logic_schema.idx_unq_cod_operador_aml"
does not exist
CONTEXT:  SQL statement "COMMENT ON INDEX
bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
unicidade do Cod_Operador_AML';"
PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
** Error **

ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml"
does not exist
SQL state: 42P01
Context: SQL statement "COMMENT ON INDEX
bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
unicidade do Cod_Operador_AML';"
PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE


Can you help me, please?
Thanks in advance
Dias Costa


Dias
 > NOTICE:  search path = {public,pg_catalog}
 >ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does 
not exist


This is not related to the clone_schema function. It looks like you may 
have corruption in your syste catalogs,

Try reindexing your system_catalogs.


Or from clone_schema.sql:

EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || 
quote_ident(source_schema) || '.' || quote_ident(object)

|| ' INCLUDING ALL)';

https://www.postgresql.org/docs/10/static/sql-createtable.html

"LIKE source_table [ like_option ... ]

...

Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original 
table will be created on the new table only if INCLUDING INDEXES is 
specified. <*/Names for the new indexes and constraints are chosen 
according to the default rules, regardless of how the originals were 
named. (This behavior avoids possible duplicate-name failures for the 
new indexes.)/*>


...
INCLUDING ALL is an abbreviated form of INCLUDING COMMENTS INCLUDING 
CONSTRAINTS INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING INDEXES 
INCLUDING STATISTICS INCLUDING STORAGE.


..."

See tagged part(<*/ /*>) part above. I could see where the indexes in 
the new schema have new names while the index comments in the old schema 
refer to the old name. Then you would get the error the OP showed.




REINDEX VERBOSE SYSTEM  ;



--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

2018-07-09 Thread Peter Geoghegan 
On Mon, Jul 9, 2018 at 11:32 AM, Andres Freund  wrote:
>> Note that there is a kludge within plan_create_index_workers() that
>> has us treat the heap relation as an inheritance parent, just to get a
>> RelOptInfo for the heap relation without running into similar trouble
>> with the index in get_relation_info(). Perhaps there's an argument to
>> be made for refactoring plan_create_index_workers() as a fix for this.
>
> Maybe I'm missing something, but what has this got to do with the issue
> at hand?

Nothing. It might be worthwhile to find a way to not do that as part
of fixing this issue, though. Just a suggestion.

> I assume we'll have to backpatch this issue, so I think it'd probably a
> good idea to put a specific CacheInvalidateHeapTuple() in there
> explicitly in the back branches, and do the larger fix in 12. ISTM
> there's some risks that it'd cause issues.  Will you tackle this?

Okay.

-- 
Peter Geoghegan

Re: How to watch for schema changes

2018-07-09 Thread Igor Korot 
Hi, Adrian

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver  wrote:
> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>
>> Adrian,
>>
>> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
>>  wrote:
>>>
>>> On 07/03/2018 10:21 AM, Igor Korot wrote:


 Hi, ALL,
 Is there any trigger or some other means I can do on the server
 which will watch for CREATE/ALTER/DROP TABLE command and after
 successful
 execution of those will issue a NOTIFY statement?
>>>
>>>
>>>
>>> https://www.postgresql.org/docs/10/static/event-triggers.html
>>
>>
>> According to the documentation the lowest version it supports is 9.3.
>> Anything prior to that?
>>
>> I'm working with OX 10.8 and it has 9.1 installed.
>
> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
> though it will go EOL this September:
>
> https://www.postgresql.org/support/versioning/
>
> Are you forced to work with 9.1 or can you use something from here:
>
> https://www.postgresql.org/download/macosx/
>
> to get a newer version? FYI that will be a major upgrade so will require a
> dump/restore or use of pg_upgrade.

Just a thought...
Is it possible to create a trigger for a system table? Or this
operation is restricted
for when the server is actually being set-up?

Successful "CREATE TABLE..." statement creates a row inside the
information_schema.tables
so if I can create a trigger after this record is inserted or deleted
that should be fine.

Thank you.

>
>>
>> And a second question - how do I work with it?
>> I presume that function will have to be compiled in its own module
>> (either dll, so or dylib).
>> But then from the libpq interface how do I call it?
>
>
> It can use functions written in PL languages. See below:
>
> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> for an example written in plpgsql.
>
>
>
>
>
>>
>> Thank you.
>>
>>>

 Thank you.

>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

2018-07-09 Thread David G. Johnston 
On Mon, Jul 9, 2018 at 1:49 PM, Igor Korot  wrote:

> Just a thought...
> Is it possible to create a trigger for a system table?
>
Not sure, and doesn't seem documented either way, but seems easy enough to
try on a test cluster...
​[...]​


> Successful "CREATE TABLE..." statement creates a row inside the
> information_schema.tables
>

​Given that information_schema.tables is a view, not a table, it doesn't
make a valid hook point for the trigger regardless of the previous point.

David J.

Re: How to watch for schema changes

2018-07-09 Thread Adrian Klaver 

On 07/09/2018 01:49 PM, Igor Korot wrote:

Hi, Adrian

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver  wrote:

On 07/03/2018 11:15 AM, Igor Korot wrote:


Adrian,

On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
 wrote:


On 07/03/2018 10:21 AM, Igor Korot wrote:



Hi, ALL,
Is there any trigger or some other means I can do on the server
which will watch for CREATE/ALTER/DROP TABLE command and after
successful
execution of those will issue a NOTIFY statement?




https://www.postgresql.org/docs/10/static/event-triggers.html



According to the documentation the lowest version it supports is 9.3.
Anything prior to that?

I'm working with OX 10.8 and it has 9.1 installed.


9.1 went EOL almost two years ago. The oldest supported version is 9.3,
though it will go EOL this September:

https://www.postgresql.org/support/versioning/

Are you forced to work with 9.1 or can you use something from here:

https://www.postgresql.org/download/macosx/

to get a newer version? FYI that will be a major upgrade so will require a
dump/restore or use of pg_upgrade.


Just a thought...
Is it possible to create a trigger for a system table? Or this
operation is restricted


Easy enough to test. As postgres super user:

test_(postgres)# create trigger info_test before insert on pg_class 
execute procedure ts_update(); 



ERROR:  permission denied: "pg_class" is a system catalog


for when the server is actually being set-up?

Successful "CREATE TABLE..." statement creates a row inside the
information_schema.tables
so if I can create a trigger after this record is inserted or deleted
that should be fine.

Thank you.





And a second question - how do I work with it?
I presume that function will have to be compiled in its own module
(either dll, so or dylib).
But then from the libpq interface how do I call it?



It can use functions written in PL languages. See below:

https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html

for an example written in plpgsql.







Thank you.





Thank you.




--
Adrian Klaver
adrian.kla...@aklaver.com




--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Cloning schemas

2018-07-09 Thread Melvin Davidson 
Adrian,
The code that CREATES the TABLE is

EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
quote_ident(source_schema) || '.' || quote_ident(object)
|| ' INCLUDING ALL)';

The schema names are supposed to be changed!

This function HAS been tested and does WORK. Please do not muddle the
problem without testing yourself.


On Mon, Jul 9, 2018 at 2:56 PM, Adrian Klaver 
wrote:

> On 07/09/2018 09:49 AM, Melvin Davidson wrote:
>
>
>>
>> On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta > > wrote:
>>
>> Hi Melvin,
>>
>> Trying run 9.6 clone_schema on a different schema and I get the
>> following error:
>>
>> NOTICE:  search path = {public,pg_catalog}
>> CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 79
>> at RAISE
>> ERROR:  relation "bilhetica_logic_schema.idx_unq_cod_operador_aml"
>> does not exist
>> CONTEXT:  SQL statement "COMMENT ON INDEX
>> bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
>> unicidade do Cod_Operador_AML';"
>> PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
>> ** Error **
>>
>> ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml"
>> does not exist
>> SQL state: 42P01
>> Context: SQL statement "COMMENT ON INDEX
>> bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
>> unicidade do Cod_Operador_AML';"
>> PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
>>
>>
>> Can you help me, please?
>> Thanks in advance
>> Dias Costa
>>
>>
>> Dias
>>  > NOTICE:  search path = {public,pg_catalog}
>>  >ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does
>> not exist
>>
>> This is not related to the clone_schema function. It looks like you may
>> have corruption in your syste catalogs,
>> Try reindexing your system_catalogs.
>>
>
> Or from clone_schema.sql:
>
> EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
> quote_ident(source_schema) || '.' || quote_ident(object)
> || ' INCLUDING ALL)';
>
> https://www.postgresql.org/docs/10/static/sql-createtable.html
>
> "LIKE source_table [ like_option ... ]
>
> ...
>
> Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original
> table will be created on the new table only if INCLUDING INDEXES is
> specified. <*/Names for the new indexes and constraints are chosen
> according to the default rules, regardless of how the originals were named.
> (This behavior avoids possible duplicate-name failures for the new
> indexes.)/*>
>
> ...
> INCLUDING ALL is an abbreviated form of INCLUDING COMMENTS INCLUDING
> CONSTRAINTS INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING INDEXES
> INCLUDING STATISTICS INCLUDING STORAGE.
>
> ..."
>
> See tagged part(<*/ /*>) part above. I could see where the indexes in the
> new schema have new names while the index comments in the old schema refer
> to the old name. Then you would get the error the OP showed.
>
>
>> REINDEX VERBOSE SYSTEM  ;
>>
>>
>>
>> --
>> *Melvin Davidson**
>> Maj. Database & Exploration Specialist**
>> Universe Exploration Command – UXC***
>> Employment by invitation only!
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Cloning schemas

2018-07-09 Thread DiasCosta 

Hi Melvin,

I followed your recommendation and it did not work.

Since I was in a rush I did try to understand where the function crashed 
and commenting in the function the creation of

comments for indexes, as follows, was sufficient for the function work.

    IF FOUND
  THEN
--    EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' 
|| quote_ident(object)

-- || ' IS ''' || v_def || ''';';

Problem is I don't usually comment indexes.

Thanks and
Keep good working
Dias Costa









On 09-07-2018 22:50, Melvin Davidson wrote:


Adrian,
The code that CREATES the TABLE is

EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || 
quote_ident(source_schema) || '.' || quote_ident(object)

    || ' INCLUDING ALL)';

The schema names are supposed to be changed!

This function HAS been tested and does WORK. Please do not muddle the 
problem without testing yourself.



On Mon, Jul 9, 2018 at 2:56 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 07/09/2018 09:49 AM, Melvin Davidson wrote:



On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta
mailto:diasco...@diascosta.org>
>> wrote:

    Hi Melvin,

    Trying run 9.6 clone_schema on a different schema and I
get the
    following error:

    NOTICE:  search path = {public,pg_catalog}
    CONTEXT:  PL/pgSQL function
clone_schema(text,text,boolean) line 79
    at RAISE
    ERROR:  relation
"bilhetica_logic_schema.idx_unq_cod_operador_aml"
    does not exist
    CONTEXT:  SQL statement "COMMENT ON INDEX
    bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
    unicidade do Cod_Operador_AML';"
    PL/pgSQL function clone_schema(text,text,boolean) line 401
at EXECUTE
    ** Error **

    ERROR: relation
"bilhetica_logic_schema.idx_unq_cod_operador_aml"
    does not exist
    SQL state: 42P01
    Context: SQL statement "COMMENT ON INDEX
    bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
    unicidade do Cod_Operador_AML';"
    PL/pgSQL function clone_schema(text,text,boolean) line 401
at EXECUTE


    Can you help me, please?
    Thanks in advance
    Dias Costa


Dias
 > NOTICE:  search path = {public,pg_catalog}
 >ERROR: relation
"bilhetica_logic_schema.idx_unq_cod_operador_aml" does not exist

This is not related to the clone_schema function. It looks
like you may have corruption in your syste catalogs,
Try reindexing your system_catalogs.


Or from clone_schema.sql:

EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
quote_ident(source_schema) || '.' || quote_ident(object)
        || ' INCLUDING ALL)';

https://www.postgresql.org/docs/10/static/sql-createtable.html


"LIKE source_table [ like_option ... ]

...

Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the
original table will be created on the new table only if INCLUDING
INDEXES is specified. <*/Names for the new indexes and constraints
are chosen according to the default rules, regardless of how the
originals were named. (This behavior avoids possible
duplicate-name failures for the new indexes.)/*>

...
INCLUDING ALL is an abbreviated form of INCLUDING COMMENTS
INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING IDENTITY
INCLUDING INDEXES INCLUDING STATISTICS INCLUDING STORAGE.

..."

See tagged part(<*/ /*>) part above. I could see where the indexes
in the new schema have new names while the index comments in the
old schema refer to the old name. Then you would get the error the
OP showed.


REINDEX VERBOSE SYSTEM  ;



-- 
*Melvin Davidson**

Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!



-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC*
Employment by invitation only!



--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não 
respeitem o
malfadado acordo

Re: Cloning schemas

2018-07-09 Thread Adrian Klaver 

On 07/09/2018 02:50 PM, Melvin Davidson wrote:


Adrian,
The code that CREATES the TABLE is

EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || 
quote_ident(source_schema) || '.' || quote_ident(object)

     || ' INCLUDING ALL)';

The schema names are supposed to be changed!

This function HAS been tested and does WORK. Please do not muddle the 
problem without testing yourself.




create table public.idx_test (id int, fld_1 varchar);
create index test_idx on idx_test (id);

test_(postgres)# \d idx_test
   Table "public.idx_test"
 Column |   Type| Collation | Nullable | Default
+---+---+--+-
 id | integer   |   |  |
 fld_1  | character varying |   |  |
Indexes:
"test_idx" btree (id)

create table sch_test.idx_test (like public.idx_test including all);

test_(postgres)# \d sch_test.idx_test
  Table "sch_test.idx_test"
 Column |   Type| Collation | Nullable | Default
+---+---+--+-
 id | integer   |   |  |
 fld_1  | character varying |   |  |
Indexes:
"idx_test_id_idx" btree (id)

When you look up the comments you do:

 SELECT oid
  FROM pg_class
 WHERE relkind = 'i'
   AND relnamespace = src_oid

Where src_oid is the source namespace/schema. So in this case:

test_(postgres)# SELECT oid, relname
  FROM pg_class
 WHERE relkind = 'i'
   AND relnamespace = 'public'::regnamespace AND oid=2089851;
   oid   | relname
-+--
 2089851 | test_idx

You then do:

 SELECT relname INTO object ..

EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)

 || ' IS ''' || v_def || ''';';

The problem is that the relname/object has changed in the new schema. In 
this case from text_idx --> idx_test_id_idx. So this happens:


test_(postgres)# comment on index sch_test.test_idx is 'test';
ERROR:  relation "sch_test.test_idx" does not exist


Just some muddling do with it what you will:)


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Cloning schemas

2018-07-09 Thread Melvin Davidson 
Dias,

As an experiment, I commented out the code that creates the comment on
indexes and it still works flawlessly, so that part is redundant.
I have attached the modified function below,
Please retry and see if the problem still exists.
If it does, then please do a schema only pg_dump of the source schema abd
send me the call to clone schema that you are using.

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  func_oid oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_cycledchar(10);

BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
  IF NOT FOUND
THEN 
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
  IF FOUND
THEN 
RAISE NOTICE 'dest schema % already exists!', dest_schema;
RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;

  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
   AND objsubid = 0;
  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
SELECT current_schemas(TRUE) INTO v_path;
RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)
  LOOP
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

EXECUTE 'SELECT last_value, max_value, start_value, increment_by, 
min_value, cache_value, log_cnt, is_cycled, is_called 
  FROM ' || quote_ident(source_schema) || '.' || 
quote_ident(object) || ';' 
  INTO sq_last_value, sq_max_value, sq_start_value, 
sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, 
sq_is_called ; 

IF sq_is_cycled 
  THEN 
sq_cycled := 'CYCLE';
ELSE
sq_cycled := 'NO CYCLE';
END IF;

EXECUTE 'ALTER SEQUENCE '   || quote_ident(dest_schema) || '.' || 
quote_ident(object) 
|| ' INCREMENT BY ' || sq_increment_by
|| ' MINVALUE ' || sq_min_value 
|| ' MAXVALUE ' || sq_max_value
|| ' START WITH '   || sq_start_value
|| ' RESTART '  || sq_min_value 
|| ' CACHE '|| sq_cache_value 
|| sq_cycled || ' ;' ;

buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
IF include_recs 
THEN
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || 
', ' || sq_is_called || ');' ; 
ELSE
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value 
|| ', ' || sq_is_called || ');' ;
END IF;

  -- add sequence comments
SELECT oid INTO tbl_oid
  FROM pg_class 
 WHERE relkind = 'S'
   AND relnamespace = src_oid
   AND relname = quote_ident(object);

SELECT description INTO v_def
  FROM pg_description
 WHERE objoid = tbl_oid
   AND objsubid = 0;

IF FOUND
  THEN  
EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
 || ' IS ''

Re: Cloning schemas

2018-07-09 Thread Adrian Klaver 

On 07/09/2018 03:23 PM, Adrian Klaver wrote:

On 07/09/2018 02:50 PM, Melvin Davidson wrote:


Adrian,




The problem is that the relname/object has changed in the new schema. In 
this case from text_idx --> idx_test_id_idx. So this happens:


test_(postgres)# comment on index sch_test.test_idx is 'test';
ERROR:  relation "sch_test.test_idx" does not exist


Just some muddling do with it what you will:)



Should have added to the above that INCLUDING ALL encompasses INCLUDING 
COMMENTS:


https://www.postgresql.org/docs/10/static/sql-createtable.html

"Comments for the copied columns, constraints, and indexes will be 
copied only if INCLUDING COMMENTS is specified. The default behavior is 
to exclude comments, resulting in the copied columns and constraints in 
the new table having no comments."


So the COMMENT ON INDEX code is redundant anyway.

--
Adrian Klaver
adrian.kla...@aklaver.com

Reconnecting a slave to a newly-promoted master

2018-07-09 Thread Shawn Mulloney 
I'm unclear on the prerequisites for what must be a very common scenario:

There are three PostgreSQL machines: A, B, and C. B and C are slaves off of
the master, A. A fails, and B is promoted to being the new master. Can C
just be pointed at A and have it "just work"?

Reporting bug on pgAdmin 4.3

2018-07-09 Thread a 
Hi


I'm doing a normal query on pgAdmin, my server platform is win server 2008, my 
laptop is win10, both using pgsql 10.


The results of the query shows 8488 rows are selected, which is correct.


However, when I drag down in data output window, it shows more than 10 
rows. 


Further test shows that each time they are different, both in number and in 
actual data.


The past and copy results are the same, even without drag down actions.


ODBC works fine, I haven't try the client.


This problem only occur on my computer, while trying on the server, it works 
fine.


Can any one provide any method to fix this??


Thanks


Shore