me know.
> Thanks,
> -Adam
>
*I am a bit curious. Why are you reinventing the wheel?*
*What is wrong with:*
*REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]*
*https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html
<https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html>*
*Can't you do that in a cron job?*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ailpref/pgsql-general
>
Perhaps you mean you want to quote of all strings? For that you use FORCE
QUOTE.
eg:
COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ','
csv HEADER QUOTE '"';
FORCE QUOTE name_first, name_last, email, company;
Please RTFM
*https://www.postgresql.org/docs/9.4/static/sql-copy.html
<https://www.postgresql.org/docs/9.4/static/sql-copy.html>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
That being said, it
is always good practice to schedule a VACUUM ANALYZE (but not FULL) in the
off hours to insure a cleanup and fresh statistics. *
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
rte.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
Just out of curiosity, rather than rely on auto_vacuum, have you considered
scheduling a cron job to do a manual vacuum / analyze in off peak hours?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
elopers :
>In Query Tool mode the control is disabled (as are the filtering options)
as you have complete control over the SQL query being executed.
Those controls are enabled in Edit Grid mode (when you right-click and View
Data on a table or view etc), where the SQL query is constructed for you.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Sep 6, 2016 at 7:28 PM, Patrick B <patrickbake...@gmail.com> wrote:
>
>
> 2016-09-07 11:25 GMT+12:00 John R Pierce <pie...@hogranch.com>:
>
>> On 9/6/2016 4:20 PM, Melvin Davidson wrote:
>>
>>> If you use slony to replicate, you CAN ha
--
> john r pierce, recycling bits in santa cruz
>
>
If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on
the slave.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
>
>>
>>
>
> --
> 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
>
Actually, in PgAdmin 4 1.0 beta4 the default is No limit and cannot
currently be changed,
at least not in Win 10.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
4/static/sql-cluster.html
<https://www.postgresql.org/docs/9.4/static/sql-cluster.html>*
*Yes, PostgreSQL does have a mechanism to cluster the index, BUT... as
stated in the docs, any subsequent insert or update will not be clustered,
so a periodic *
*reCLUSTER is required to maintain it.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
r correct address
entry at
https://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html
Also, please note that although it does not apply in this case, it is
always helpful to provide O/S and PostgreSQL version when addressing this
mail list.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
; http://www.postgresql.org/mailpref/pgsql-general
>
You need to change your entry from this
host mattermost mmuser 127.0.0.1 md5
to this
host mattermost mmuser 127.0.0.1*/32* md5
or this
host mattermost mmuser 127.0.0.1*/24* md5
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
sedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailp
essional privilege and copyright. No representation is made that this
> email is free of viruses or other defects. If you have received this
> communication in error, you may not copy or distribute any part of it or
> otherwise disclose its contents to anyone. Please advise the sender of your
> incorrect receipt of this correspondence.
>2016-08-12 03:04:00 GMT [23188]: [9-1] user=,db=,client= (0:XX000)FATAL:
invalid memory alloc request size 3445219328
I'm not sure, but I'd double check your shared_memory spec both in
postgresql.conf and /proc/sys/kernel/shmmax
(or /etc/sysctl.conf) in DR
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
relnamespace)
>>> JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
>>> WHERE idx.indisvalid = FALSE
>>> ORDER BY 1, 2;
>>>
>>> nspname | relname | indexrelname | type | ?column?
>>> -+-+--+--+-
t cover PostgreSQL for novices and advanced users. No one is going to
spoon feed you and there is no such thing as a "Vulcan Mind Meld" to give
you instant knowledge.
Like any fine tool, you have to spend some time to learn how to use it
properly.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
here transactionid in (75315815)
> order by transactionid; returns me a record with transactionid 75315811???
>
> Thanks,
> ioana
>
>
>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
You did provide the full table structure, but this migh
cal location, and the other two different
> country.
>
>
>two of them in the same physical location, and the other two different
country.
And the time difference is on ALL slaves, or just the two in a different
country?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
le
>> reload."
>>
>> <adrian.kla...@aklaver.com>
>
>
> Cheers... I removed the IONICE command from the archive_command. However,
> did not see any difference.
>
> Any idea?
>
>
Just out of curiostity, are the slaves in the same physical location,
e Task Manager show is running?
>
> When I was talking about system logs I meant the OS logs, I was not clear
> on that point.
>
>
> --
> 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
>
*> 2016-08-02 17:05:59 BRT LOG: database system was not properly shut>
down; automatic recovery in progress<2016-08-03 08:42:39 BRT FATAL: lock
file "postmaster.pid" already exists>2016-08-03 08:42:39 BRT TIP: Another
postmaster (PID 2968) is running under the data directory "C: / Program
>Files / PostgreSQL / 9.5 / data"?These errors point to the source of your
problem. When the database is shutdown imporperly(as in pulling the plug or
manual power down), you often get left with the postmaster.pid file not
beeing deleted.So just go ahead and delete
/PostgreSQL/9.5/data/postmaster.pid and you can then restart the PostgreSQL
service.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
want to shut off my car engine but keep driving.I
believe you are looking at the wrong solution. The best way to migrate and
keep all transactions is to use Slony
replication.http://slony.info/adminguide/2.2/doc/adminguide/slony.pdf
<http://slony.info/adminguide/2.2/doc/adminguide/slony.pdf>http://slony.info/downloads/2.2/
<http://slony.info/downloads/2.2/>You can start the migration to the new
server and when it's finished, you simply failover to the slave and
redirect the url to it. *
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson <melvin6...@gmail.com>
> wrote:
>
>> Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide us
>> with a li
ying to do since, I can't make it work again.
>
> Can you please advise me what to do to make this work?
>
> Best regards.
> --
> Léa Massiot
>
>
>
> --
> 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.
versions of Postgres?
>
> Where they installed the same way?
>
> You mentioned the log feed showing obvious performance issues, can we see
> the relevant portions?
>
>
>> cheers
>> Tom
>>
>>
>
> --
> 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
>
*I have to ask, was a vacuumdb -Z OR psql -U postgres -c ANALYZE ; *
*done after the migration?*
*Without accurate stats, performance goes down the drain.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
O/S
2. Gave a comprehensive explanation of exactly what you are trying to do.
IOW, What exactly are you trying to prevent users from doing and why?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
names of the other databases and
connection info.
Then the same trigger that calls the function for NOTIFY or
pg_notify(text, text) could just as
easily call a function with dblink that determines which database needs the
notify and raises it there.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
iting a file with the same name from a different
cluster.
Think of Ghostbusters ->"Don't cross the streams"!.
There is no reason why you cannot have separate directories for each
cluster/tablespace, so why ask for trouble?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether o
url that shows you pg_dump and pg_dump are
what is needed.
But if you don't believe me, I will comment no further.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
d compatible from 7.0?
> I mean can i restore a dump made with pg_dump 7.0 on a 9.4 database?
>
> --
> Willy-Bas Loos
>
Yes, That is the whole point of backwards compatability! That is how
upgrades are done.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 07/15/2016 07:07 AM, Willy-Bas Loos wrote:
>
>>
>> On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson <melvin6...@gmail.com
>> <mailto:melvin6...@gmail.com>>
On Fri, Jul 15, 2016 at 10:07 AM, Willy-Bas Loos <willy...@gmail.com> wrote:
>
> On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson <melvin6...@gmail.com>
> wrote:
>
>>
>> Why can't he just do a
>> pg_dump -F p his_dbname > his_dbname.sql
>> Th
laptop
copy the data from the jump/thumb drive to the new laptop
create the new db in 9.5
and use pg_restore to load the his_dbname.sql ?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
s nothing to do with my questions.*
*Have you even tuned the postgresql.conf?*
*You cannot fairly compare PostgreSQL with any other database unless you
first tune it's postgres.conf.*
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
stgresql.conf?
Also, what is the exact version of PostgreSQL you are using?
What is the total time to complete the test for all 3 DB's?
The best I can tell is that with all the unknowns, you are comparing apples
to oranges.
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
may do it that way. But why copy-paste directly to psql results in this
> behaviour?
>
>
> Dmitry Shalashov, surfingbird.ru & relap.io
>
> 2016-07-07 20:30 GMT+03:00 Melvin Davidson <melvin6...@gmail.com>:
>
>> This might seem a bit basic, but as long as y
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>
>...I `cat` it, copy it to buffer, go to my beloved psql and insert it
there.
This might seem a bit basic, but as long as you have a psql session, why
not just use
*\i your_file_name *
No need to cat, copy & paste!
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
em is that it is a duplicate of the
production. So even if
you think you have it working, you are just asking for trouble when the
time comes to do a failover. Seriously, decide on one O/S and use it.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
e. The options you have at connect time are documented
here:
https://www.postgresql.org/docs/9.3/static/app-psql.html
All other commands/statements must be executed after connect, specified
with the -c option or included in a file with -f .
Note the exceptions that can be specified per user as
timeout, that cannot be set at
the command line. You must execute that AFTER you connect.
ie: # SET statement_timeout = 1000;
You can also place multiple commands inside a file and then execute that
after you connect.
eg: # \i your_filename.sql
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pecific
> question.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> <https://www.visena.com>
>
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
than 24h ( -mmin +1440 )
It may be working, but that is not the supported method for wal file
retention. The correct thing to do is just
incrtease the wal_keep_segments as per the documentation
https://www.postgresql.org/docs/9.2/static/runtime-config-replication.html
>FYI PostgreSQL 9.4 and later provide "replication slots", which will
ensure that all WAL
>files are kept until no longer required by standbys:
*replication slots will not work, as you are on 9.2.*
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
t;
>
>
>>
>>
>> Please, in the future, remember to state your *PostgreSQL version and
>> O/S* as options can change.
>>
>>
> Ok.. my mistake.. I'm running PostgreSQL 9.2 in a Centos 6.7 64 bits.
>
>That means there will be at least 256 files before postgres delete them?
*Yes!*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS>
on the master and reload the postgresql.conf.*
*https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS
<https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS>*
Please, in the future, remember to state your *PostgreSQL version and O/S*
as options can change.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
aster to be
sufficiently highly so that the problem does not occur again.
Once you make the change, be sure to reload the config file on the master
Either
SELECT pg_reload_conf();
or
pg_ctl reload -D your_data_dir
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Mon, Jun 20, 2016 at 1:53 PM, Vik Fearing <v...@2ndquadrant.fr> wrote:
> On 20/06/16 17:25, Melvin Davidson wrote:
> >>And you haven't read Vik's reply. :)
> >
> > Yes I have. Vacuum wll not lock all tables at once, only the ones it is
> > currently working
On Mon, Jun 20, 2016 at 11:18 AM, Martín Marqués <mar...@2ndquadrant.com>
wrote:
> El 20/06/16 a las 12:06, Melvin Davidson escribió:
> >
> > Martin and Vik,
> >
> >>...Think about a SELECT which has to scan all child tables.
> >
> > You are rea
l@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG <http://openscg.com>*
> http://openscg.com
>
Martin and Vik,
>...Thin
;
>
>
> but it won't let it grow too (or am I missing something).
Yes, you are missing something. By partioning and {Vacuum Full only the
table with data no longer needed}, the rest of the data remains available
to the users
AND space is reclaimed by the O/S, so it's the best of both worlds.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
S, as
improvements to VACUUM may play a role here.
Is there any reason you cannot partition the table? Moving the data to
separate partitions
(based on a date or key field) will allow you to vacuum full only 1
partition at a time.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ptop (windows 7/10) and remote server.
>
>
> Best regards,
> Catalin Mafteiwww.plationline.euwww.livrarionline.rowww.c-solution.biz
>
> Skype: c-solution
> Skype: catalinmaftei
> Mobile: +40723 338 598
>
> On 6/17/2016 2:55 AM, Melvin Davidson wrote:
>
>
>
> On Wed, Jun 15, 2016 at
ubscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
*You have not provided information as to the O/S of the PostgreSQL server
or your PgAdmin remote system (I suspect Windows), but regardless, you are
just asking for trouble by doing development over a remote connection.You
s
greSQL-Developer-Guide-Ibrar-Ahmed/dp/1783989025/ref=sr_1_8?s=books=UTF8=1466006737=1-8=PostgreSQL>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
t; --
> 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 Adrian's questions,
What is the O/S you are using?
How much total memory is in the system?
What are the v
to file, perhaps SET status = 2, so that you don't
> write the same file multiple times.
>
> 3) If you are worried about throttling, put a pause into the program
> looping through backup_table. You have to write a program to read
> backup_table in order to supply your function with
changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Please, you should specify your PostgreSQL version and O/S for questions
like this.
As a solution to your problem, have you considered using a VIEW with a
WHERE clause similar to
WHERE your_date > current_date - interval '7 days' ?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Thu, Jun 9, 2016 at 5:03 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:
>
>
> On Thu, Jun 9, 2016 at 5:01 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Thu, Jun 9, 2016 at 4:45 PM, Alex Magnum <magnum11...@gmail.com>
>>
CT r.rulename,
c.description
FROM pg_rewrite r
JOIN pg_description c ON c.objoid = r.oid;
-- To get all trigger comments
SELECT DISTINCT t.tgname,
c.description
FROM pg_trigger t
JOIN pg_description c ON c.objoid = t.oid;
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
rimary_conninfo, but, it can be false.
>
> Regards,
>
> Bertrand
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
he new and modern cloud-inspired paradigms out there, our
> traditional architecture might not of much interest any more, still I would
> love to make the above happen some time.
>
> Best Regards,
>> CN
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>
>
> --
> 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.
r over the web
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Your points make no sense. You can accomplish the same with:
GRANT ROLE user2 TO user1
cannot tell is someone is intentionally messing with the file
system. You have removed only the first file node with rm 32809.
*First off, you should never do that.* *If you want to drop the table, then
do DROP TABLE t5;*
*That will drop all the file nodes for that table.*
*You may as well as
and
> Slony to see if they might fit the bill as well.
>
> Thanks again for the replies.
>
> Jeff
>
> On Fri, May 27, 2016 at 5:18 PM Melvin Davidson <melvin6...@gmail.com>
> wrote:
>
>>
>>
>> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin <tarh
CentOS release 6.2
> Postgres 8.3
>
> Kind Regards,
> Jeff
>
> On Fri, May 27, 2016 at 5:05 PM Melvin Davidson <melvin6...@gmail.com>
> wrote:
>
>>
>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin <tarheelj...@gmail.com>
>> wrote:
>>
since you are migrating from 8.3, you are
limited to pg_dump in plain format.
It would be nice (important) to know the PostgreSQL version you are
migrating to, as well as what O/S you are working with.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
d replace with new
information.
Most DBA's usually specify a date(and/or time) as a suffix to prevent that
probleb and keep several versions of backup.
Not that it in this case, but it is considered good procedure to report the
PostgreSQL version and O/S when contacting this list for support.
Please consider
gt;>> > Can you please check ?
>>>
>>> The query seems right to me.
>>>
>>> Cheers,
>>>
>>> Jeff
>>>
>>
>> I think the query is ok.. just wanna understand if that value is correct
>> :O
>>
>> If it is..
> SELECT *
> FROM (
> SELECT bar
> FROM foo
> ORDER BY bar desc
> LIMIT 1
> ) y;
>
> DROP TABLE foo;
>
Seems to me SELECT min(), max() FROM deja.vu ; would also
work.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
_dat)
VALUES
( 1,'++'),
( 2,'| __ ___|'),
( 3,'| /)/ \/ \ |'),
( 4,'| ( / ___\) |'),
( 5,'| \(/ o) ( o) ) |'),
( 6,'| \_ (_ ) \ ) / |'),
( 7,'| \ /\_/\)_/|'),
( 8,'| \/ //| |\\ |'),
( 9,'| v | | v |'),
(10,'|\__/|'),
(11,'||'),
(12,'| PostgreSQL 1996-2016 |'),
(13,'| 20 Years of success |'),
(14,'++');
SELECT row_dat FROM elephant ORDER BY row_num;
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ond: (("title")::"text" ~~* 'RYAN
>> SHOWER%'::"text")
>> Buffers: shared hit=6835
>> Total runtime: 4439.427 ms
>
>
> Here [1] it appears to be working even with two %.. But it's not for
> me
>
OmniPITR to
> archive xlogs and make them available in case of loss of connection.
>
> --
> To understand recursion, one must first understand recursion.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
&
Bitmap Index Scan on "ix_ja_jobs_trgm_gin"
>> (cost=0.00..335.63 rows=484 width=0) (actual time=3720.213..3720.213
>> rows=32 loops=1)
>> Index Cond: (("title")::"text" ~~ '%RYAN
>> WER%'::"text")
>> Total runtime: 3720.653 ms
>
>
>
> so.. the query is still slow..
> Do you guys know what can be done ? related to the ILIKE?
>
> cheers
> Lucas
>
The main problem is WHERE title ILIKE '%RYAN WER%'
When you put a % on the left of the text, there is no way to optimize that,
so yes, it will be slow.
If you can eliminate the leading percent and just have trailing, it will be
much faster.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
es AS idstat
>> JOIN pg_indexes ON indexrelname = indexname
>> JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
>> WHERE indexrelname = ' {YOUR QUERY NAME } ';
>>
>>
> Sorry.. not sure what I should put into the WHERE clause ..
>
> But
indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
temp directory from
/var/lib/pgsq/whatever to /tmp
Have you checked the postgres log to see if there are any errors about it
not being able to write to the pg_stat_temp dir?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ontent.
Has the size / # rows changed recently? If the planner thinks it can load
all the rows faster, it will use a seqscan regardless if you have an index.
If that is the case, you can force index use by doing a
SET enable_seqscan = off
before executing the query.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
.. the index wasn't being used
> from those statistics
> 7 - ?
>
>
> So, my problem is: the statistics are not running? What happened to the
> statistics?
>
> Do you guys know how can I update the stats?
>
My crystal ball is not working, you have a PostgreSQL version
into using PgBadger. It gives you a great deal
of info about your queries, including I/O, above and beyond pg_stats.
PgBadger info <https://dalibo.github.io/pgbadger/>PgBadger download
<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.
ciates your honesty, until you are honest with them.
>
>
> --
> 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.
y suggest you learn how to debug your own code.
The best way to do that is to temporarily use RAISE NOTICE ... at various
points in your TRIGGER function so that YOU can determine the exact source
of the problem.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
What might I cover that I haven't mentioned?
Well, I'm pretty sure that one of the reasons Web Developers do not use SQL
is because they do not know what is in the database. Perhaps a sections
that teaches them how to list the tables and columns
from the INFORMATION_SCHEMA would be a good start.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
rd is it to change 1 to 1000?
INSERT INTO company_seqs
(company_id, last_seq )
VALUES
( {whatever_new_company_id_id}, 1000};
Really, you need to start thinking for yourself, but first _learn database
design_! That is why I recommended those books to you.
>not sure what v_seq_num is...
On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote:
>
>>
>>
>> On 4 May 2016 at 01:18, Melvin Davidson <melvin6...@gmail.com
>> <mailto:melvin6...@gmail.com>> wrote
iously, get yourself the books I have recommended and study them BEFORE
you continue attempting to design your database.
You need a firm understanding of logical design & flow, otherwise you will
be wasting your time.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
exes used to
> cluster tables (indisclustered).
>
> You should also check since when the idsx_scan and other counters are
> aggregating before dropping any index. Check
> pg_stat_get_db_stat_reset_time(oid), with the oid of the related
> database(s).
>
> >
> >
> > Cheers
> > Lucas
>
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
WHERE NOT idx.indisprimary
AND i.relname NOT LIKE 'pg_%'
AND i.idx_scan + idx_tup_read + idx_tup_fetch = 0
ORDER BY i.indexrelname;
I would not place any concern on the size of the index. That is just what
is needed to keep track of all associated rows.
Once you drop the indexes you determi
DEX CONCURRENTLY ix_inodes_checksum_st_size ON gorfs.inodes
> USING btree ("checksum_md5","st_size");
>
Two things to consider.
1. Did you remember to run ANALYZE on the table after the new indexes were
created?
2. Try doing a
SET enable_seqscan = off;
before executing th
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 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.
On Sun, May 1, 2016 at 6:31 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Sunday, May 1, 2016, Melvin Davidson <melvin6...@gmail.com> wrote:
>
>>
>> Your index is based on split_part function
>> but the WHERE clause is specific to full_path, s
> application as the DB might not even see the payload
>> media_subtype_id integer, -- Reference to MIME type (see FK
>> constraint). We can't support all media types but unknow types can be
>> stored as application/octet-stream
>> external_size "gorfs"."off_t", -- For symlinks only. Meaningful for fat
>> links only: total size of the fat link target. Null for normal symlinks
>> CONSTRAINT pk_inodes PRIMARY KEY ("st_ino"),
>> CONSTRAINT fk_media_subtype_must_exist FOREIGN KEY (media_subtype_id)
>> REFERENCES public.media_subtypes (media_subtype_id) MATCH SIMPLE
>> ON UPDATE NO ACTION ON DELETE NO ACTION,
>> CONSTRAINT cc_mount_devices_not_supported CHECK ("st_dev" IS NULL)
>> )
>
>
>
> 4. Did you do an ANALYZE table gorfs.inode_segments after you created
>> the index?
>>
>
> Yes.. actually the index was already created.
>
Well, it looks like David's explanation is correct.
Your index is based on split_part function
but the WHERE clause is specific to full_path, so the planner cannot find a
valid index
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
.466..51428.439 rows=31 loops=1)"
>> " Filter: ("split_part"(("full_path")::"text", '/'::"text", 4) =
>> '12225'::"text")"
>> " Rows Removed by Filter: 104361402"
>> "Total runtime: 51428.482 ms"
>
>
> Cheers
> Lucas
>
>
Well, a little more information would be useful like:
1. What is the PostgreSQL version?
2. What is the O/S?
3. What is the structure of gorfs.inode_segments?
4. Did you do an ANALYZE table gorfs.inode_segments after you created the
index?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
it is pertinet.
Now try this:
SELECT n.nspname, c.relname as table
FROM pg_class c
JOIN pg_namespace n ON (n.oid =c.relnamespace )
WHERE relkind = 'r' AND
relname NOT LIKE 'pg_%' AND
relname NOT LIKE 'sql_%' AND
relhaspkey = FALSE
ORDER BY n.nspname, c.relname;
--
*
On Mon, Apr 25, 2016 at 8:41 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 04/25/2016 05:29 PM, Stephen Frost wrote:
>
>> * Melvin Davidson (melvin6...@gmail.com) wrote:
>>
>>> Hmmm, if you go back a few comments, you will note that per initdb -
On Mon, Apr 25, 2016 at 8:18 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Monday, April 25, 2016, Stephen Frost <sfr...@snowman.net> wrote:
>
>> Melvin,
>>
>> * Melvin Davidson (melvin6...@gmail.com) wrote:
>> > So in essence &qu
On Mon, Apr 25, 2016 at 8:05 PM, Stephen Frost <sfr...@snowman.net> wrote:
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > Then could you please clarify exactly what structural mods are permitted
> by
> > *"*
> > *Allows modification of the structure o
On Mon, Apr 25, 2016 at 7:57 PM, Stephen Frost <sfr...@snowman.net> wrote:
> David, Melvin,
>
> * David G. Johnston (david.g.johns...@gmail.com) wrote:
> > On Monday, April 25, 2016, Melvin Davidson <melvin6...@gmail.com> wrote:
> > > I need clarification
On Mon, Apr 25, 2016 at 7:50 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Monday, April 25, 2016, Melvin Davidson <melvin6...@gmail.com> wrote:
>
>> I need clarification on allow_system_table_mods parameter
>> Per the documentation:
>> *
t;> http://www.postgresql.org/mailpref/pgsql-general
>>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
On Mon, Apr 25, 2016 at 4:26 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:
> >my previous answer did not make sense as postgresql.conf is set up by
> initdb
> Yeah, that kind of puzzles me too.
>
> >-O allow_system_table_mods = on
> Thanks, I'll give that
Klaver <adrian.kla...@aklaver.com>
wrote:
> On 04/25/2016 09:30 AM, Melvin Davidson wrote:
>
>> I need clarification on allow_system_table_mods parameter
>> Per the documentation:
>> *Allows modification of the structure of system tables.* This is used by
>> initdb. This
-25 12:08:14 EDT LOG: terminating any other active server processes
>From ntstatus.h
*#define STATUS_ACCESS_VIOLATION ((NTSTATUS)0xC005L) //
winnt*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Sat, Apr 23, 2016 at 1:03 AM, Shulgin, Oleksandr <
oleksandr.shul...@zalando.de> wrote:
> On Apr 22, 2016 19:46, "Melvin Davidson" <melvin6...@gmail.com> wrote:
> >
> >
> > First, tahnk you for your feedback Alex.
> >
> > "IMO, every
201 - 300 of 621 matches
Mail list logo
