Re: [GENERAL] Incremental refresh - Materialized view

2017-11-07 Thread Melvin Davidson
version. As previously stated, there is currently no such thing as an incremental refresh of a materialized view. I believe what you are looking for is: *REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_name;* REFRESH MATERIALIZED VIEW <https://www.postgresql.org/docs/9.5/static/sql-refreshmaterializ

Re: [GENERAL] Naming conventions for column names

2017-11-06 Thread Melvin Davidson
* *therefore I must strongly advise against any other unnecessary column* *renames in system catalogs.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

2017-10-21 Thread Melvin Davidson
/pg_extractor <https://github.com/omniti-labs/pg_extractor>* *https://www.keithf4.com/pg_extractor/ <https://www.keithf4.com/pg_extractor/>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

2017-10-21 Thread Melvin Davidson
yourdb.lis and comment out ( prefix with ; ) any line you don't want* *4. then use pg_restore with the edited list-file * *EG: pg_restore -L yourdb.lis yourdb.dmp* *See examples at the end of* *https://www.postgresql.org/docs/9.6/static/app-pgrestore.html <https://www.postgresql.org/d

Re: [GENERAL] How to find out extension directory

2017-10-20 Thread Melvin Davidson
a connection to the database you want to create the extension in and do *CREATE EXTENSION extention_name;* https://www.postgresql.org/docs/10/static/sql-createextension.html -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Melvin Davidson
ason described by David G Johnston. The fact that PostgreSQL 10 was only released this month is critical and therefore is should not be a production server. It should be used as development, or QA, at best. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Adding identity column to a non-empty table

2017-10-15 Thread Melvin Davidson
On Sun, Oct 15, 2017 at 9:09 PM, Igal @ Lucee.org <i...@lucee.org> wrote: > Melvin, > > On 10/15/2017 5:56 PM, Melvin Davidson wrote: > > > On Sun, Oct 15, 2017 at 8:24 PM, Igal @ Lucee.org <i...@lucee.org> wrote: > >> On 10/15/2017 4:01 PM, Igal @ Lucee.o

Re: [GENERAL] Adding identity column to a non-empty table

2017-10-15 Thread Melvin Davidson
_id the primary key would be: ALTER TABLE event_log ADD COLUMN r_id SERIAL; ALTER TABLE event_log ALTER COLUMN r_id TYPE BIGINT, ADD CONSTRAINT dummy_pk PRIMARY KEY (r_id); That automatically generates the column as r_id bigint NOT NULL DEFAULT nextval('dummy_r_id_seq'::regclass), CONSTRAINT dummy_pk

Re: [GENERAL] Non-overlapping updates blocking each other

2017-10-15 Thread Melvin Davidson
; 'ff0f----' > > > and > > > UPDATE [...] WHERE id BETWEEN 'f8c0----' > AND > > > 'f8ff----' > > > Yet one blocks the other one. How is this possible? > > On Sat, Oct 14, 2017, at 12:32 PM, Me

Re: [GENERAL] Non-overlapping updates blocking each other

2017-10-14 Thread Melvin Davidson
On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > Melvin Davidson schrieb am 14.10.2017 um 17:32: > >> More than likely, the optimizer has determined that a table scan is best, >> in which case it will use a table lock. >> That means one

Re: [GENERAL] Non-overlapping updates blocking each other

2017-10-14 Thread Melvin Davidson
d and not l1.granted) LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted) LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid) LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid) WHERE pg_backend_pid() <> c.pid ORDER BY datname, query_start; -- *Melvin Davi

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

2017-10-13 Thread Melvin Davidson
quot;part" and store the results in the primary one, which also seems easier with multiple >schemas than multiple databases. If that is what you need to do, then definitely use multiple schemas. In PostgreSQL, the only way to do cross db queries / DML, is with the dblink extension, and from personal use, it is a PIA to use. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] "Shared strings"-style table

2017-10-13 Thread Melvin Davidson
On Fri, Oct 13, 2017 at 12:52 PM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere <sea...@abshere.net> > wrote: > >> > > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: >> > >> Theo

Re: [GENERAL] "Shared strings"-style table

2017-10-13 Thread Melvin Davidson
strings" like this, reducing size on > disk > > >> at the cost of lookup overhead for all queries? > > >> (I guess maybe it's like TOAST, but content-hashed and de-duped and > not > > >> only for large objects?) > > On Fri, Oct 13, 2017, at 0

Re: [GENERAL] "Shared strings"-style table

2017-10-13 Thread Melvin Davidson
r.street_name, r.city, s.state_name FROM residence r JOIN residence_type t ON t.residence_id = r.residence_id JOIN state s ON s.state_id = r.state_id WHERE residence_id = 12345; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Determine size of table before it's committed?

2017-10-11 Thread Melvin Davidson
( a.oid = c.relowner ) WHERE relname = 'x' ORDER BY total_size_bytes DESC, 1, 2; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Melvin Davidson
of the entire PostgreSQL data file directory. That includes all PostgreSQL system catalogs as well as user objects. A restore of your data directory would be faster than a full restore of pg_dump as indexes and constraints would not have to be recreated. However, it does not give you the option to restore

Re: [GENERAL] delete a file everytime pg server starts/crashes

2017-10-08 Thread Melvin Davidson
.org/docs/9.6/static/functions-info.html>* *9.25. System Information Functions* *pg_postmaster_start_time()* *It does not matter what caused the failure, it is the time Postgres is started again.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] delete a file everytime pg server starts/crashes

2017-10-08 Thread Melvin Davidson
>... I'm creating the filename as pg_start_time... Just an FYI, you do know that SELECT pg_postmaster_start_time(); will return start time for postgres? -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] delete a file everytime pg server starts/crashes

2017-10-05 Thread Melvin Davidson
version(). That being said, have you looked at the documentation for "shared_preload_libraries" ? *https://www.postgresql.org/docs/9.6/static/runtime-config-client.html <https://www.postgresql.org/docs/9.6/static/runtime-config-client.html>* -- *Melvin Davidson* I reserve t

Re: [GENERAL] time series data

2017-10-02 Thread Melvin Davidson
I want a report. > > Clifford > > On Sun, Oct 1, 2017 at 10:08 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> >> On Sun, Oct 1, 2017 at 4:17 AM, Khalil Khamlichi < >> khamlichi.kha...@gmail.com> wrote: >> >>> Hi everyo

Re: [GENERAL] time series data

2017-10-01 Thread Melvin Davidson
ECT call_time FROM current_session WHERE call_status = 'after_call') ) as duration FROM user_sessions WHERE username = '*actual_user_name*' AND session_id = *actual_session_id*; You can use similar queries for avg and frequency. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Converting inherited partitions into declarative ones

2017-09-29 Thread Melvin Davidson
ning the way > things stand now. > > Thanks in advance. > > > -- > Victor Yegorov > *>Is it possible to avoid Full Scan here? * *Have you verified that constraint_exclusion is set to "on" or "partition" ?* *https://www.postgresql.org/docs/9.6/st

Re: [GENERAL] Function to return per-column counts?

2017-09-28 Thread Melvin Davidson
stanullfrac as pct_null, s.stadistinct FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_statistic s ON (s.starelid = c.oid AND s.staattnum = a.attnum) WHERE c.relname = 'your_table_name' AND a.attnum > 0 ORDER BY 3 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Is auto-analyze as thorough as manual analyze?

2017-09-23 Thread Melvin Davidson
ema.your_table SET (autovacuum_vacuum_scale_factor = 0.5); ALTER TABLE some_schema.your_table SET (autovacuum_vacuum_threshold = 1000);* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Why can't the database owner create schemas and how can I enable that?

2017-09-23 Thread Melvin Davidson
e changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Make sure you are the DB owner when you connect. Either psql -U api_user -d api_development OR psql -d api_development SET ROLE api_user; SELECT current_user; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

2017-09-19 Thread Melvin Davidson
also something > we should be considering for core, especially now that we've got a > reasonable password-based authentication method with SCRAM. > > Thanks! > > Stephen > Perhaps, as an alternative, although not currently supported, connection attempts can be added in the future

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

2017-09-18 Thread Melvin Davidson
On Mon, Sep 18, 2017 at 9:30 AM, Ron Johnson <ron.l.john...@cox.net> wrote: > On 09/18/2017 08:17 AM, Melvin Davidson wrote: > [snip] > > I don't have any specific suggestion for an additional column, other than > Berend's idea. However, I strongly advise against the use

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

2017-09-18 Thread Melvin Davidson
t then filled in as the events occur. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > I don't have any specific suggestion for an additional column, other than Berend's idea. However, I strongly advise against the use of ENUM's. They can create a major problem in the event one needs to be removed. It is a lot easier and simpler to use a Foreign Key table instead. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Numeric numbers

2017-09-02 Thread Melvin Davidson
LE <https://www.postgresql.org/docs/9.6/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE>* *YOU have specified a precision of numeric(100,90), which means 90 decimals and that is exactly what you got!* *The result is correct, so what is your question?* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Table create time

2017-08-31 Thread Melvin Davidson
On Thu, Aug 31, 2017 at 11:46 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> wrote: > >> On 31/08/2017 18:20, Melvin Davidson wrote: >> >> >you could

Re: [GENERAL] Table create time

2017-08-31 Thread Melvin Davidson
On Thu, Aug 31, 2017 at 11:26 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 31/08/2017 18:20, Melvin Davidson wrote: > > >you could just create an event trigger looking for CREATE TABLE as > filter_value: > > I have tried that. Unfortunately, I

Re: [GENERAL] Table create time

2017-08-31 Thread Melvin Davidson
On Thu, Aug 31, 2017 at 11:19 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Melvin Davidson <melvin6...@gmail.com> writes: > > Wolfgang, as David said, a column in pg_class for the creation time of a > > table does not exist. I long ago requested that feature as it is >

Re: [GENERAL] Table create time

2017-08-31 Thread Melvin Davidson
@gmail.com > wrote: > On Thu, Aug 31, 2017 at 10:21 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > Wolfgang, as David said, a column in pg_class for the creation time of a > table does not exist. I long ago requested that feature as it is > > in other DB's (Orac

Re: [GENERAL] Table create time

2017-08-31 Thread Melvin Davidson
amespace) JOIN pg_authid a ON ( a.OID = c.relowner ) WHERE n.nspname NOT LIKE 'information%' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'information%' AND relname NOT LIKE 'sql_%'AND relkind = 'r'AND d.description IS NULLORDER BY 1, 2;* -- *Melvin Davidson* I reserve t

Re: [GENERAL] clustered index benchmark comparing Postgresql vs Mariadb

2017-08-30 Thread Melvin Davidson
ur test? B. Did you verify the index was being used with EXPLAIN your_query? C. What was the exact query you used? D. Most important, what is the structure of the table and index? E. How much system memory is there? F. In postgresql.conf What are the settings for 1. shared_memory 2. wor

Re: [GENERAL] Get user defined type OID (PostgreSQL extension in C)

2017-08-25 Thread Melvin Davidson
e get_fn_expr_argtype because I am > not passing the new type as argument but creating it in the function > itself, correct ? > *>Is there a function to get the id of my new type? Like get_oid_elem (struct geo_trajc_elem)* *A simple* *SELECT oid, typname* * FROM pg_type* * WHERE typname =

Re: [GENERAL] What is the proper query

2017-08-22 Thread Melvin Davidson
> > Hopefully this above will not be mangled and the spacing will be kept. > > Thank you. > > > > > David J. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Deadlocks

2017-08-22 Thread Melvin Davidson
ql-general > *I don't think you will get much help unless you show us the query and the structure* *of the tables involved.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] make postgresql 9.5 default on centos 7

2017-08-18 Thread Melvin Davidson
export PATHhttps://www.postgresql.org/docs/9.1/static/install-post.html <https://www.postgresql.org/docs/9.1/static/install-post.html>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Queries for Diagramming Schema Keys

2017-08-14 Thread Melvin Davidson
.postgresql.org/docs/9.6/static/catalogs.html <https://www.postgresql.org/docs/9.6/static/catalogs.html>* *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Queries for Diagramming Schema Keys

2017-08-14 Thread Melvin Davidson
S* *SELECT n.nspname as schema,t.relname as table,c.conname as fk_name FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE relkind = 'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE 'sql_%' ORDER BY n.nspname, t.relname, c.conname;* *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Help on Index only scan

2017-08-13 Thread Melvin Davidson
On Sun, Aug 13, 2017 at 7:37 PM, Ertan Küçükoğlu < ertan.kucuko...@1nar.com.tr> wrote: > > On 14 Aug 2017, at 01:15, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu < > ertan.kucuko...@1nar.com.tr> wrote

Re: [GENERAL] Help on Index only scan

2017-08-13 Thread Melvin Davidson
eral > *First, you do not need index "updates_autoinc", since autoinc is the Primary Key, you are just duplicating the index.* *As far as "Index only scan" , since the table only has 2003 rows, the optimizer has determined it is faster just to* *load all the rows into memory and then filter. If you really want to force an index scan, then you would have to do* *SET enable_seqscan = off; Before doing the query, however you are just shooting yourself in the foot by doing that* *as it will make the query slower.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_stat_statements -- Historical Query

2017-08-10 Thread Melvin Davidson
ur subscription: > http://www.postgresql.org/mailpref/pgsql-general > *If you are interested in historical stats, you would probably fair a lot better with PgBadger. It is free* *and highly customizable. In addition to SQL call rates at different times, it provides analysis of* *most used querie

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Melvin Davidson
On Wed, Aug 9, 2017 at 3:32 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> *>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well*

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Melvin Davidson
On Wed, Aug 9, 2017 at 3:00 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> >> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston < >> david.g.johns

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Melvin Davidson
J. > > *David,* *The problem is, The current owner of the extension needs to be dropped. No one should have to jump through hoops* *just to be able to do that. There is definitely a need for an* *ALTER EXTENSION name OWNER TO new_owner.* *As Tom Lane has already pointed out, it would not be hard to add that.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Melvin Davidson
On Wed, Aug 9, 2017 at 12:19 PM, Scott Marlowe <scott.marl...@gmail.com> wrote: > On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Melvin Davidson <melvin6...@gmail.com> writes: > >> *UPDATE pg_extensionSET extowner =

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Melvin Davidson
On Wed, Aug 9, 2017 at 11:20 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart <colinth...@gmail.com> > wrote: > >> Hi, >> >> Why does >> ALTER EXTENSION name OWNER TO new_owner; >> n

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Melvin Davidson
e having a particular problem (other than owner) with an extension, it would be helpful to* *post a script to illustrate that. You should be able to drop the role without any problem. * *If an error occurs, then please advise on that and include the exact message.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

2017-08-08 Thread Melvin Davidson
table (and header if there is one) *Have you looked at the TUPLES ONLY option?* *-t* *--tuples-only* *Turn off printing of column names and result row count footers, etc. This is equivalent to the \t command.* *https://www.postgresql.org/docs/9.4/static/app-psql.html <https://www.postgresql

Re: [GENERAL] Audit based on role

2017-08-07 Thread Melvin Davidson
://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)

2017-08-05 Thread Melvin Davidson
ved in that column. > >> > >> > >> Dan Cooperstock > >> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com > >> Email: i...@software4nonprofits.com > >> Phone: 416-423-

Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)

2017-08-05 Thread Melvin Davidson
<(416)%20423-7722> > Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada > > *If you do not want to receive any further emails from > Software4Nonprofits, please reply to this email with the subject line > "UNSUBSCRIBE", and we will immediately remove you from our mailing list, if > you are on it.* > > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command

2017-08-02 Thread Melvin Davidson
ic/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL <https://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Schemas and serials

2017-07-29 Thread Melvin Davidson
ot;. All of them have a primary key of type serial. >Later, I will copy the tables definitions to production.* *A word of caution, creating tables in a qa "schema" and then transferring to production is not the normal/correct (or safe) way to do development.* *The standard procedure is

Re: [GENERAL] I can't cancel/terminate query.

2017-07-13 Thread Melvin Davidson
On Thu, Jul 13, 2017 at 2:45 PM, Edmundo Robles <edmu...@sw-argos.com> wrote: > i executed the commands many times like superuser but that queries > still running :( > > On Thu, Jul 13, 2017 at 11:10 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >>

Re: [GENERAL] I can't cancel/terminate query.

2017-07-13 Thread Melvin Davidson
e_backend, you need to be a superuser.* *So first;* *SET ROLE postgres;* Then you should be able to *SELECT **pg_terminate_backend(); * Where is the pid of process you want to termnate. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Get table OID

2017-07-13 Thread Melvin Davidson
On Thu, Jul 13, 2017 at 11:06 AM, Igor Korot <ikoro...@gmail.com> wrote: > Hi, Melvin, > > On Thu, Jul 13, 2017 at 10:42 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> On Thu, Jul 13, 2017 at 10:36 AM, Igor Korot <ikoro...@gmail.com> wrot

Re: [GENERAL] How to drop column from interrelated views

2017-07-09 Thread Melvin Davidson
he columns and then use that to redefine the views BEFORE dropping the column from the table. IE: in the following query, replace with the name of the column to be dropped. 1. Make a SQL pg_dump of the database before proceeding 2. \o edit_views.sql SELECT 'CREATE OR REPLACE VIEW ' || n

Re: [GENERAL] Trying to reduce xlog wal files

2017-07-06 Thread Melvin Davidson
general > *>I have updated postgresql.conf checkpoint_segments from 1000 to 250* *But what is the value of wal_keep_segments?* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

2017-07-05 Thread Melvin Davidson
On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost <sfr...@snowman.net> wrote: > Greetings, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > Stephen, > > >This script is a good example of why trying to take a PG backup using > > shell scripts isn't a good i

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

2017-07-05 Thread Melvin Davidson
you have an alternate solution or can make improvements to it, then I am sure the op and I would welcome them. On Wed, Jul 5, 2017 at 9:10 AM, Stephen Frost <sfr...@snowman.net> wrote: > Greetings, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > On Tue, Jul 4, 2017 a

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

2017-07-04 Thread Melvin Davidson
WAL generated during the > backup. > > As for existing solutions, my preference/bias is for pgBackRest, but > there are other options out there which also work, such as barman. > > Thanks! > > Stephen > Here is a model shell script I use to do a base backup to set up a slave.

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-01 Thread Melvin Davidson
disvalid THEN 'valid' ELSE 'INVALID' END as statusi, pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname)) as size_in_bytes, pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname))) as size FROM pg_stat_all_indexes i JOIN pg_class c ON (c.oid = i.relid) JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) WHERE i.relname LIKE '%%' AND n.nspname NOT LIKE 'pg_%' ORDER BY 1, 2, 3; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-30 Thread Melvin Davidson
ff using tcp_keepalives... , providing your system supports it. I don't remember if you ever gave us * *the O/S.* *Have a look at them. You might possibly be able to use them to force disconnect after a set amount of inactivity time.https://www.postgresql.org/docs/current/static/runtime-config-con

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-06-30 Thread Melvin Davidson
. >> >> >> -- >> Timokhin 'maf' Maxim >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > *Since you are doing a major version upgrade, the correct way to do that, depending on the size of your DB, is * *A. pg_dumpall on old version and pg_relo

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-29 Thread Melvin Davidson
ot;CASE WHEN waiting ..." needs to be > commented out..* > *The second is a Linux script "kill_long_idles.sh" that will kill any > connection that is * > *'idle in transaction' for longer than max_time. Currently it is set to 30 > minutes, but you can adjust to* > > * y

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-06-29 Thread Melvin Davidson
CASCADE ON DELETE CASCADE > TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) > REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE > > > Everything would be not bad if in the table weren't appeared duplicated > records in url column. > Any idea how is it possible? > > Thank you! > > -- > Timokhin 'maf' Maxim > > *It's possible you have index corruption on 9.4.8 version that was not detected.Try the following query on 9.4.8 to see if any rows are selected. Then you can decide how to fix from there.SELECT a.id <http://a.id>, a.url, b.id <http://b.id>, b.url FROM items a, items b WHERE a.id <http://a.id> <> b.id <http://b.id> AND a.url = b.url ORDER by a.id <http://a.id>;* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread Melvin Davidson
t; -- > Adrian Klaver > adrian.kla...@aklaver.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *His problem is NOT 'idle in transaction' per se. It is all connections are used up.* *Hence the need for pg_bouncer for connection pooling.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread Melvin Davidson
general > *Are you asking how to track down the user using up all the connection? With the information you provided that cannot. be down.* *If you are asking how to prevent problems in the future, then install Pg_Bouncer and use that to pool connections.https://pgbouncer.github.io/ <https://

Re: [GENERAL] 10beta1 role

2017-06-22 Thread Melvin Davidson
> Adrian Klaver > adrian.kla...@aklaver.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > It would be useful to know the O/S and PostgreSQL ve

Re: [GENERAL] Schedule

2017-06-20 Thread Melvin Davidson
keyed to location. > We already have a monitoring system in place that has been in operation > circa 2003. Just recently we have > added a new class of customer whose operation is not 24/7. > > I envision the schedule could be fairly complicated > including WE and holidays, plus the enduser

Re: [GENERAL] Remote connection to PostgreSQL

2017-06-19 Thread Melvin Davidson
gt; -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > In addition to enabling listen_addresses, you also have to add entries in pg_hba.conf. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] effective_io_concurrency increasing

2017-06-18 Thread Melvin Davidson
gt; > 2017-06-19 13:19 GMT+12:00 Melvin Davidson <melvin6...@gmail.com>: > >> >> >> On Sun, Jun 18, 2017 at 9:02 PM, Patrick B <patrickbake...@gmail.com> >> wrote: >> >>> Hi guys. >>> >>> I just wanna understand the effective_

Re: [GENERAL] effective_io_concurrency increasing

2017-06-18 Thread Melvin Davidson
Thanks > P > *Perhaps you should read the doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html <https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html>* 18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)* *Melvin Davidson* I reserve th

Re: [GENERAL] ERROR: type " " does not exist

2017-06-12 Thread Melvin Davidson
relatively small function I'd spend more effort trying to > figure out a better way than just doing it brute force. > > David J. > > *You did not state the O/S you are using, but if it's LINUX, just use vi and do a "set list".* *That should reveal any strange characters tha

Re: [GENERAL] Does NUMERIC lose precision?

2017-05-29 Thread Melvin Davidson
ilpref/pgsql-general > >While the above operation works fine once I divide 1 by that number the result is an inexact decimal number: ># SELECT 1 / (2::NUMERIC ^ 64); ? ?column? > >0.00054210108624275222 >(1 row) *That is the same

[GENERAL] Memorial Day

2017-05-29 Thread Melvin Davidson
*On this Memorial Day, Let us not forget the many that gave their lives so that we can enjoy our Freedom today. I give thanks to all my fellow veterans, and those still on active duty. May future generations still remember us and grow to never know the horrors of war.Da Nang 1969->'70* -- *Mel

Re: [GENERAL] Help with terminology to describe what my software does please?

2017-05-28 Thread Melvin Davidson
e)) FROM pg_database) ) * 100)::numeric(6,3) AS pct FROM pg_database ORDER BY datname; schema comparison is a lot more complication as it involves comparing collations domains functions trigger functions sequences tables types views -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Call for users to talk about table partitioning

2017-05-21 Thread Melvin Davidson
he old master is renamed and the new master is renamed in place of the old master. That means the only downtime is during the renaming, whichis minimal. In the event a problem occurs, you can easily reverse the renames, and since * *the old master will still contain all original records, the risk is minim

Re: [GENERAL] Call for users to talk about table partitioning

2017-05-18 Thread Melvin Davidson
'd love to set up some time to > learn about your needs with this feature. Let us know some times that work > for you and we'll send over details for the call. > > Thanks! > Shirley > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] database is not accepting commands

2017-05-16 Thread Melvin Davidson
PostgreSQL - general mailing list archive at Nabble.com. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresq

Re: [GENERAL]

2017-05-11 Thread Melvin Davidson
On Thu, May 11, 2017 at 9:24 AM, Igor Korot <ikoro...@gmail.com> wrote: > Melvin et al, > > On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> On Wed, May 10, 2017 at 11:26 PM, Igor Korot <ikoro...@gmail.com> wrot

Re: [GENERAL]

2017-05-11 Thread Melvin Davidson
> > > > > > > > -- > > john r pierce, recycling bits in santa cruz > > > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *Igor,* *as others have suggested, you would be better off querying the system catalogs to get constraint information. The query below is what I use.* *Perhaps it will help you modify to your needs.SELECT cn.conname, CASE WHEN cn.contype = 'c' THEN 'check' WHEN cn.contype = 'f' THEN 'foreign key'WHEN cn.contype = 'p' THEN 'primary key'WHEN cn.contype = 'u' THEN 'unique'WHEN cn.contype = 't' THEN 'trigger'WHEN cn.contype = 'x' THEN 'exclusion' END as type, cn.condeferrable, CASE WHEN cn.conrelid > 0 THEN (SELECT nspname || '.' || relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.oid = cn.conrelid)ELSE '' END as table, confkey, consrc FROM pg_constraint cn ORDER BY 1;* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] relation create time

2017-05-10 Thread Melvin Davidson
*Also, I have been requesting that feature for a long time, so if you want to add your support, vote for it athttps://postgresql.uservoice.com/forums/21853-general?filter=top=2 <https://postgresql.uservoice.com/forums/21853-general?filter=top=2>* *and look for relcreate* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Melvin Davidson
> log_cnt | 29 > > is_cycled | f > > is_called | t > > > > Regards, > > Max > > > > *From:* Melvin Davidson [mailto:melvin6...@gmail.com] > *Sent:* Tuesday, 2 May 2017 9:49 AM > *To:* Adrian Klaver <adrian.kla...@aklaver.com> &

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Melvin Davidson
understood on this, now I am not sure. Do you mean that the >> actual values in the id column in all the tables have been set to 1 or that >> the serial sequence that controls the id value has been set to 1? >> >> >>> >>> >>> Regards, >>>

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Melvin Davidson
larify things, please execute and attach the output from the following query.SELECT sequence_schema, sequence_name, start_value, maximum_value, cycle_optionFROM information_schema.sequences ORDER BY 1, 2; * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Melvin Davidson
> > I am very new to PostgreSQL and this mail list. Please let me know if > > I did not something wrong. > > > > > > > > Thank you. > > > > > > > > Regards, > > > > Max > > > > > -- > Adrian Klaver > adrian.kl

Re: [GENERAL] GENERAL : [Can somebody explain about Base directory]

2017-05-01 Thread Melvin Davidson
es have you created in your PostgreSQL server?-- * *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] PostgreSQL Required Monitoring

2017-04-28 Thread Melvin Davidson
//www.postgresql.org/mailpref/pgsql-general > One thing I always monitor is index sizes, usage and status. See attached pg_stat_all_indexes.sh -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. pg_stat_all_indexes.sh Descr

[GENERAL] The Contractor Conundrum

2017-04-26 Thread Melvin Davidson
nly advise that aspiring contractors find out as much about a system/schema/policies before commiting to a contract, no matter how much they offer to pay. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Total ram size study

2017-04-22 Thread Melvin Davidson
and the > difference was very remarkable. > > On Sat, Apr 22, 2017 at 11:27 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> >> >> *Has anyone ever done a study on performance increase via ram increase?I >> have a client on AWS with 8G

[GENERAL] Total ram size study

2017-04-22 Thread Melvin Davidson
*Has anyone ever done a study on performance increase via ram increase?I have a client on AWS with 8GB total ram (2GB shared_buffers), and I amcurious if doubling the ram to 16GB (4GB shared_buffers) will result in minimizing query response time.* -- *Melvin Davidson* I reserve the right

Re: [GENERAL] Clone PostgreSQL schema

2017-04-18 Thread Melvin Davidson
On Tue, Apr 18, 2017 at 3:48 AM, R. Reiterer <r.reite...@posteo.at> wrote: > Hi Melvin, > > after a first test, the function seems to work perfect! MANY THX!!! > > Regards, > > Reinhard > > Am 17.04.2017 17:21 schrieb Melvin Davidson: > >> On Mon,

Re: [GENERAL] Clone PostgreSQL schema

2017-04-17 Thread Melvin Davidson
On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer <r.reite...@posteo.at> >

Re: [GENERAL] Clone PostgreSQL schema

2017-04-17 Thread Melvin Davidson
On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer <r.reite...@posteo.at> wrote: > >> Hi Melvin, >> >> thanks again for your help! I did some testing, but views in the new

Re: [GENERAL] Clone PostgreSQL schema

2017-04-16 Thread Melvin Davidson
On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer <r.reite...@posteo.at> wrote: > Unfortunately, I do not have the skills to improve the function. Maybe > someone at dba.stackexchange.com can help me. I'll open a ticket. I hope > this is okay for you. > > Am 16.04.2017 22:31 sc

  1   2   3   4   5   6   7   >