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-refreshmaterializedview.html>
Specifying CONCURRENTLY with prevent locking of the underlying table(s),
but will extend the
time it takes to complete the refresh.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ke changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
*All,*
*Please take note that when PG 9.2 was release, a column rename of*
*"procpid" to just "pid" in pg_stat_activity DID break prior user code,*
*therefore I must strongly advise agai
//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.
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/docs/9.6/static/app-pgr
base 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.
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.
On Sun, Oct 15, 2017 at 9:09 PM, Igal @ 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 wrote:
>
>> On 10/15/2017 4:01 PM, Igal @ Lucee.org wrote:
>>
>>>
>>>
:
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 PRIMARY KEY (r_id)
and creates the appropriate sequence for you.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
; 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, Melvin Davidso
On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer
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 updates will be blocking
locks l1 ON (c.pid = l1.pid 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 Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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.
On Fri, Oct 13, 2017 at 12:52 PM, Melvin Davidson
wrote:
>
>
> On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere
> wrote:
>
>> > > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote:
>> > >> Theoretically / blue sky, could there be a table or colu
ducing 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 01:29 PM, Melvin Davidson
ty,
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.
elname) as
total_size_bytes,
CASE WHEN c.reltablespace = 0
THEN 'pg_default'
ELSE (SELECT t.spcname
FROM pg_tablespace t WHERE (t.oid = c.reltablespace)
)
END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.r
tem 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 one or more selected objects.
pg_dump, on the other hand, gives you the flexibility of selecting what to
backup and what to restore. FWIW, I much prefer pg_dump.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
atic/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.
reating 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.
ion().
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 the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Clifford
>
> On Sun, Oct 1, 2017 at 10:08 AM, Melvin Davidson
> wrote:
>
>>
>>
>> On Sun, Oct 1, 2017 at 4:17 AM, Khalil Khamlichi <
>> khamlichi.kha...@gmail.com> wrote:
>>
>>> Hi everyone,
>>>
>>> I have a data st
ly best to use a BEFORE trigger to do this, but you will need to
code it yourself.
2. You can then do
SELECT username,
age ( (SELECT call_time FROM current_session WHERE call_status =
'talking'),
( SELECT 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.
e declarative partitioning 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
,
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.
acuum_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.
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.
e, 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 to "Event Triggers"?
Users could then cr
On Mon, Sep 18, 2017 at 9:30 AM, Ron Johnson 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
> of ENUM's. T
to the word_games table,
> again, allowing NULL, but 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.
atype-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.
On Thu, Aug 31, 2017 at 11:46 AM, Melvin Davidson
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 just create an event trigger l
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 have
On Thu, Aug 31, 2017 at 11:19 AM, Tom Lane wrote:
> Melvin Davidson 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
> > in other DB's (Oracle & MS SQL Server
hu, Aug 31, 2017 at 10:21 PM, Melvin Davidson
> 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 (Oracle & MS SQL Server), but the main reason that
N
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 the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to 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. work_memory
3. All Planner Cost Constants values, All Genetic Query Optimizer values
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
sible to use 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*
* WHER
.
>
> 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.
u 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.
.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.
st doc.*
*https://www.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.
OIN pg_namespace n ON (n.oid = t.relnamespace) WHERE relkind =
'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname, t.relname, c.conname;*
*-- TABLES and FKEYS*
*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.
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 wrote:
>
>
> On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu <
> ertan.kucuko...@1nar.com.tr> wrote:
>
>> Hello,
>>
> http://www.postgresql.org/mailpref/pgsql-general
>
*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.
nterested 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 queries, slowest queries, etc.*
*https://sourceforge.net/projects/pgbadger/
<https://sourceforge.net/projects/pgbadger/>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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
> wrote:
>
>> *>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well*
>>
>> *Possibly,
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
> wrote:
>
>>
>>
>> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>
em 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.
On Wed, Aug 9, 2017 at 12:19 PM, Scott Marlowe
wrote:
> On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane wrote:
> > Melvin Davidson writes:
> >> *UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE
> extowner =
> >> {oid_from_above_statement};*
> >
>
On Wed, Aug 9, 2017 at 11:20 AM, Melvin Davidson
wrote:
>
> On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart
> wrote:
>
>> Hi,
>>
>> Why does
>> ALTER EXTENSION name OWNER TO new_owner;
>> not exist?
>>
>> I have a bunch of extensions
ticular 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.
output of the empty
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:
Data - http://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.
gt; --------
> >> Dan Cooperstock
> >> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
> >> Email: i...@software4nonprofits.com
> >> Phone: 416-423-7722
> >> Mail: 57 Gledh
its.com
> Email: i...@software4nonprofits.com
> Phone: 416-423-7722 <(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.
-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.
ter, 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 to create a seperate "qa" database (and/or
server)
On Thu, Jul 13, 2017 at 2:45 PM, Edmundo Robles
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
> wrote:
>
>>
>>
>> On Thu, Jul 13, 2017 at 11:57 AM, Edm
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.
On Thu, Jul 13, 2017 at 11:06 AM, Igor Korot wrote:
> Hi, Melvin,
>
> On Thu, Jul 13, 2017 at 10:42 AM, Melvin Davidson
> wrote:
>
>>
>> On Thu, Jul 13, 2017 at 10:36 AM, Igor Korot wrote:
>>
>>> Hi, ALL,
>>> Is it possible to get the tabl
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.nspname || '.' || c.relname || '
ated 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.
On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost 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 idea.
> >
>
stion. If 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 wrote:
> Greetings,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > On Tue, Jul 4, 2017 at 5:55 PM, Steph
to do WAL replay of the 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 ba
;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.
much better off 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/stat
; --
>> 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_reload on new
t; *CAVEAT EMPTOR! in PG 9.6 the "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
&g
ADE 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.
n 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.
king 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://pgbouncer.github.io/>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
a...@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 version.
That way we can direct y
ation.
> 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 might
hanges 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.
*As per the docs:*
*1. This is dependent on whether or not you are using a RAID disk,2. "Some
experimentation may be needed to find the best value"*
*IOW, there is no general recommendation.*
On Sun, Jun 18, 2017 at 9:24 PM, Patrick B wrote:
>
>
> 2017-06-19 13:19 GMT+12
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 the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
perience and a 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 a
lpref/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 answer you get when you us
*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*
--
SUM(
pg_database_size(datname))
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.
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
minimal.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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.
ist 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.postgresql.org/mailpref/pgsql-genera
On Thu, May 11, 2017 at 9:24 AM, Igor Korot wrote:
> Melvin et al,
>
> On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson
> wrote:
>
>>
>> On Wed, May 10, 2017 at 11:26 PM, Igor Korot wrote:
>>
>>> Hi, John et al,
>>>
>>> On Wed, May 1
27;v'::"char",
> > 'f'::"char"])) AND (pg_has_
> > role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum,
> > 'SELECT, INSERT, UPDATE, REFERENCES'::text));
> >
> >
> >
> > --
> > 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.
on
time.*
*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&page=2
<https://postgresql.uservoice.com/forums/21853-general?filter=top&page=2>*
*and look for re
| 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
> *Cc:* Max Wang ; Amitabh Kant ;
> pgsql-general@postgr
; 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,
>>>
>>> Max
>>>
>>>
>>>
>>
>>
>>
>
> --
> 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
>
*Hmmm, it's beginning to look like someone did a "SELECT setval('seqname',
1);" For every sequence. *
*Is that a possibility?*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
t 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.
ist. Please let me know if
> > I did not something wrong.
> >
> >
> >
> > Thank you.
> >
> >
> >
> > Regards,
> >
> > Max
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via
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.
Everyone appreciates your honesty, until you are honest with them.
> Unless otherwise stated, opinions are my own.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-genera
can only 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.
the
> difference was very remarkable.
>
> On Sat, Apr 22, 2017 at 11:27 AM, Melvin Davidson
> wrote:
>
>>
>>
>>
>> *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
>
*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 to
On Tue, Apr 18, 2017 at 3:48 AM, R. Reiterer 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, Apr 17, 2017 at 11:02 AM, Melvin D
On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson
wrote:
>
>
> On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson
> wrote:
>
>>
>> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer
>> wrote:
>>
>>> Hi Melvin,
>>>
>>> thanks again
On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson
wrote:
>
> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer wrote:
>
>> Hi Melvin,
>>
>> thanks again for your help! I did some testing, but views in the new
>> schema still refer to the old schema.
>>
>>
On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer 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 schrieb Melvin Davidson:
&
1 - 100 of 626 matches
Mail list logo
