ting the schema dump
> while in service but possibly not in this scenario. Plan B is to drop a lot
> of tables and deal with imports later.
>
> I take the above to mean that a lot of the tables are cruft, correct?
>
> >
> > I appreciate the help.
> >
>
>
> -
hindra.com/Disclaimer.html externally
> http://tim.techmahindra.com/tim/disclaimer.html internally within
> TechMahindra.
>
>
> ========
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
ry and confidential and subject to the Tech Mahindra policy
> statement, you may review the policy at
> http://www.techmahindra.com/Disclaimer.html <
> http://www.techmahindra.com/Disclaimer.html> externally
> http://tim.techmahindra.com/tim/disclaimer.html <
> http://tim.techmahindra.com/tim/disclaimer.html> internally within
> TechMahindra.
> >
> >
>
> >
> >
> >
>
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
SELECT relfilenode FROM pg_class WHERE relname = 'earlyprinttuples';
>
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
--
*Melvin Davidson*
I res
',
> t_name || 'tab_id_seq',
> t_name || '_cdc');
>
> EXECUTE format('ALTER TABLE %I ALTER COLUMN table_id SET DEFAULT
> nextval(%L);',
> t_name || '_cdc',
> t_name || 'tab_id_seq');
>
> David J.
>
>
*Do you mean ?*
*https
.6/static/app-pgrestore.html>*
*Otherwise, your next option would be trying to use PITR recovery,
providing you have available WAL files.*
*https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
<https://www.postgresql.org/docs/9.6/static/continuous-archiving.html>*
--
*M
ce?
>
>
>
>how are others handling this with postgresql ?
One technique is to replicate to a slave on another server and do the
pg_dump 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.
y table/tables?
>
> Thank you!
> F
>
>How can i see what is locking my table/tables?
The attached query will give you all the necessary info.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
SELECT l.databa
an update query.
>
> Thank you again!
> F
>
> ____
> Da: Melvin Davidson [melvin6...@gmail.com]
> Inviato: sabato 2 dicembre 2017 16.22
> A: Job
> Cc: pgsql-gene...@postgresql.org
> Oggetto: Re: Problems with triggers and table lock
>
> On Fri, Dec 1
talogs.html
You might also find it easier to look at the information_schema
<https://www.postgresql.org/docs/9.6/static/information-schema.html>
https://www.postgresql.org/docs/9.6/static/information-schema.html
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
space)
> WHERE n.nspname='ap' and C.relkind='r' ORDER BY C.relname ;
>
> On Mon, Dec 4, 2017 at 9:20 PM, Melvin Davidson
> wrote:
>
>>
>>
>> On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar
>> wrote:
>>
>>> Hi ,
>>>
r question cannot be answered unless you also provide
A. The explain plan in question.
B. The structure of all indexes involved
C, Have you run ANALYZE on all tables involved?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
rtoli #
#Se tu m'ami
cbiylm11#O Leggiadri Occhi Belli#Cecilia Bartoli #
#Se tu m'ami
cbiylm12#Il mio bel foco #Cecilia Bartoli
# #Se tu m'ami
Then all you really need is:
SELECT *
FROM ari
ty(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 = 'your_tab
uote_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 = &
regards, tom lane
>
>
Jeremy ,
per Tom
>Seems like you could build a query...
Attached is the query that I use. Hope that helps you.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
SELECT n.nsp
pages.
> 0 pages are entirely empty.
> CPU: user: 2.59 s, system: 4.37 s, elapsed: 41.46 s.
> INFO: analyzing "sui.segmentset_member"
> INFO: "segmentset_member": scanned 3 of 171478 pages, containing
> 3599972 live rows and 0 dead rows; 3 rows in sample, 20540396
ny databse and information system
That is not exactly true. AFAIK, only Oracle has a Percentage function. SQL
Server and MySQL do not.
It has already been shown that it is just as easy to code percentage
inline (EG: SELECT (50.8 x 5.2) / 100 AS pct; ## .026416
as it is to call a function SELECT pct(50.8, 5.2);
Please do not false statements to justify a request for a non-needed
enhancement.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ml
<https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html>https://www.pgadmin.org/docs/pgadmin4/dev/debugger.html
<https://www.pgadmin.org/docs/pgadmin4/dev/debugger.html>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
gt; --
>> Mike Nolan
>>
>
>
*>Can you describe what you mean by log statements? *
*What he probably means is make use of the RAISE NOTIFY statement.*
*https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
<https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
of such a solution ?
>
> Thank you,
>
> Olivier
>
>- use ENUMs to reduce the storage space
First, please include the PostgreSQL version and O/S when communicating
with this list
Next, I recommend you avoid ENUMs and instead use Foreign Keys. ENUMs are
old tech from before
RITVS
>
>
Aside from the simple explanations you have received, I question your
justification for even having a temporary function.
Functions are only entries in the system catalogs and as such, take up just
a tiny amount of physical space. In addition,
if you ever need it again, you will have to
ard to maintain. I also
know from experience that MySql does not check
integrity of enums. It's possible you can have data in a MySql table column
that is not valid for current enum constraint on that column.
EG: When porting, the enum for a columm (VALID) was "yes, no", but I found
a
SQL.
PostgreSQL will prevent any insert where the value of a column is not
within the FK table.
So you DO NOT need a check constraint or trigger.
What I do not understand is your reference to a FK "array".
So for the sake of CLARIDICATION, would you please
1, State the version of PostgreSQL
2. State the O/S
3. Provide an example of an FK "array" that you are concerned with.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ay DATE,
driver_first_name text,
driver_last_name text,
final_positions integer,
CONSTRAINT races_driver_fk FOREIGN KEY (driver_first_name,
driver_last_name)
REFERENCES drivers (driver_first_name, driver_last_name)
);
and that is available,
which is why I requested clarifica
On Sat, Dec 23, 2017 at 2:27 PM, Peter J. Holzer wrote:
> On 2017-12-23 13:14:50 -0500, Melvin Davidson wrote:
> > On Sat, Dec 23, 2017 at 12:49 PM, Ken Tanzer
> wrote:
> >
> > I thought the OP was asking for this feature ("Support for Array
&
nb_post integer NOT NULL default '1',
last_post_datetimestampNOT NULL,
last_post_login varchar(70) NOT NULL,
CONSTRAINT sqmf_thread_pk PRIMARY KEY (thread_id)
);
CREATE INDEX sqmf_thread_idx
ON sqmf_thread
USING BTREE (forum_id);
Note: in PostgreSQL datetime is timestamp.
Also sqmf_stat_pk, sqmf_stat_uq, sqmf_thread_pk and sqmf_thread_idx are
just suggested names, but all constraint & index names must be unique
https://www.postgresql.org/docs/9.6/static/sql-createtable.html
In the futuire, please include PostgreSQL version & O/S
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
t_activity;
Otherwise, I would give you a query tailored to your version of PostgreSQL.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ON of PostgreSQL you are using, I
will point you to the latest release.*
*The WITH clause is part of the standard SELECT statement.*
*Just search for "WITH Clause" in this url.*
*https://www.postgresql.org/docs/current/static/sql-select.html#SQL-WITH
<https://www.postgresql.org/docs/curr
data is really there?
>
> You could write such a function, but it wouldn't be able to be general
> purpose as a zero-byte file is, in fact, a valid file. You could just
> as easily do a 'select 1 from table limit 1;' and make sure that you get
> back a successfu
On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost wrote:
> Greetings,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > >There should be a "catalog" that point where tables are stored in
> physical
> > files...
> >
> > Here is the query that
y do not try to degrade the efforts of others.
On Fri, Dec 29, 2017 at 9:22 PM, Stephen Frost wrote:
> Melvin,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost
> wrote:
> > > * Melvin Davidson (melvin6...@gmail.com
ng, but
doing nothing yourselves to help.My query works as designed and has done so
for two years. It shows the filenames for schemas and tables in the
database. If you don't like it,TOO BAD! Shame on you! Use your brains and
write your own query. Nuff said, I shall reply no more. *
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ot;some stay in the state waiting forever"*
*Don't forget to create indexes on the FK's in the table they reference!*
*Also, it would be nice to know the PostgreSQL version and O/S.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
SQL version and O/S*
*B. Your two examples appear exactly the same.*
*C. Have you looked at https://donatstudios.com/CsvToMarkdownTable
<https://donatstudios.com/CsvToMarkdownTable> ?*
*Found with a google search of sql markdown table*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
y_code)
lower(admin1)
lower(name)
lower(city)
That being said, you are better off forcing lowercase on all fields
BEFORE inserting into the table.
EG:
INSERT INTO test_table VALUES (lower(some_key), lower(name),
lower(feature_class), )
Then you would would not need to use lower() in the indexes or the query.
Please, in the future, always include your version of PostgreSQL and O/S
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
ery (A): SELECT id FROM events ORDER BY events.id
<http://events.id> DESC LIMIT 1 - I'm saving the result as a "max ID" for
the second query*
*Just a suggestion. The first query is not really needed.*
*You can simply do:*
*second query (B): *
*SELECT id, ... FROM events WHERE id > MIN(ID)AND id <= MAX(ID)
AND ...*
*See https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
<https://www.postgresql.org/docs/9.6/static/functions-aggregate.html> *
*MAX and MIN functions*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Wed, Jan 17, 2018 at 10:45 AM, Jacek Kołodziej
wrote:
>
>
> 17.01.2018 3:30 PM "Melvin Davidson" napisał(a):
>
>
>
> On Wed, Jan 17, 2018 at 8:08 AM, Jacek Kołodziej
> wrote:
>
>> Hello everyone,
>>
>> this is my first post her
FROM pg_tablespace t
WHERE (t.oid =
c.reltablespace) )
END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE n.nspname NOT LIKE 'pg_%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
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.
g/docs/9.6/static/sql-savepoint.html
https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html
https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
completely and starting again.
>
> and I'm looking on more information on *that*.
>
>
> On 26.01.2018 15:59, Melvin Davidson wrote:
> > On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz >
> > wrote:
> >
> >> I'm currently doing a small writeup of a
int
>> insert into B but fail
>> rollback to savepoint
>> insert into C
>> commit
>>
>> Otherwise the transaction is, after the failed statement, in a state in
>> which it
>> can not be used anymore. Is that correct?
>
>
> Yes.
>
> David J.
>
>
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
david.g.johns...@gmail.com> wrote:
> On Fri, Jan 26, 2018 at 9:27 AM, Melvin Davidson
> wrote:
>
>> >
>>
>>> As far as I'm aware neither PostgreSQL nor OS version do matter for this
>>
>>
>> Yes as of this date. However, that is not to s
rpriseDB. I saw a demo of it a few years ago and it was quite
impressive, *
*but I've had no interaction with it since, so you will have to judge for
yourself.*
*https://sourceforge.net/projects/gridsql/?source=navbar
<https://sourceforge.net/projects/gridsql/?source=navbar>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
s based on other
views. *
*Multiple reasons are stated here:*
*https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design
<https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design>*
*To summarize, you suffer performance degradation, colu
ABC database”. But schema
> created with in postgres database not with in ABC database.
>
> What should be the proper script ?
>
>
>
> Thanks and Regards,
>
> Abhra
>
>
*> But schema created with in postgres database not with in ABC database. *
*Just specify
t;**"** postgres*
>
> *su **-c* *"**psql -c **\"**create schema authorization **\"**"*
> * postgres*
>
> *fi*
>
>
> Hi Melvin,
> I executed the above script but schema created with
> in postgres db not with in ABC db. And in terminal didn&#
t;>
>> Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
>> correctly. But, again, not necessary for any of the other tables.
>>
>> This problem is not a transient fluke - I can reproduce it in two
>> different databases on different servers that wer
On Thu, Feb 8, 2018 at 1:17 PM, Melvin Davidson
wrote:
>
>
> On Thu, Feb 8, 2018 at 1:09 PM, Adrian Klaver
> wrote:
>
>> On 02/08/2018 09:58 AM, Steven Hirsch wrote:
>>
>>> On Thu, 8 Feb 2018, Francisco Olarte wrote:
>>>
>>> Something must
T ON TABLE udm_asset_type_definition TO asset_registry_readers;
>> GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
*FYI, further down in Sequence Manipulation Functions is*
*" **currval*
*Return the value most recently obtained by nextval for this sequence in
the current session. (An error is reported if nextval has never been called
for this sequence in this session.) Because this is returning a
session-local value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did.*
* " *
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
the documention
8.6. Upgrading a PostgreSQL Cluster
*https://www.postgresql.org/docs/current/static/upgrading.html
<https://www.postgresql.org/docs/current/static/upgrading.html>*--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
> To clarify, you need to use logical replication here since the WAL format
> is not usable across versions.
>
> pg_upgrade is your simplest option if you can handle its downtime.
>
> David J.
>
> To clarify, you need to use logical replication here since the WAL format
bouncer>*
*I used PgBouncer in a few of ny previous positions and found it to be very
fast and efficient.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
time.sqlpg_stat_all_indexes.sqlpg_stat_all_tables.sqltable_sizes.sqltable_stats.sqluseless_indexes2.sqlPlease
also note I have bash script versions of the same, butas you did not state
the O/S, I felt the sql was best.*--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to
d
> load is low and it will be easier to manage fewer servers). All in all, it
> isn't as bad as it could be or as bad as I've seen before, so it shouldn't
> bee too bad. Establishing some standards and change control will help.
>
> thanks again,
>
> tim
>
On Fri, Feb 16, 2018 at 10:46 AM, Basques, Bob (CI-StPaul) <
bob.basq...@ci.stpaul.mn.us> wrote:
> Melvin,
>
> Thanks for posting these. I haven’t even looked at them yet and just
> grabbed them based on the names. :c)
>
>
> On Feb 15, 2018, at 9:11 PM, Melvin Davi
;>
>>> Any guidance is much appreciated here.
>>>
>>>
>>> Thanks in Advance!
>>>
>>>
>>>
>>> --
>>>
>>> Regards,
>>> Azim
>>>
>>>
>>
>> --
>> Adrian Klaver
>>
t; Tim
>>
>>
>> --
>> Tim Cross
>>
>>
>
> "Only two things are infinite, the universe and human stupidity, and I'm
> not sure about the former.”
> - Albert Einstein
>
>
>
>
>
*> resources for scripts to perform basic database audits an
N (n.oid =
c.relnamespace)JOIN pg_authid o ON (o.oid = c.relowner)WHERE n.nspname
not like 'pg_%' AND n.nspname not like 'inform_%' AND relkind =
'r'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.
tput that
> pg_dump displays.
>
> ex:
>
> GRANT ALL ON TABLE testing_cdc TO bob;
>
>
> --
> -- PostgreSQL database dump complete
> --
>
> I need a way which my script can isolate the owner's name and set it to a
> variable on its own. Same with grant.
>
s-and-everything-you-need-to-know/>*
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
101 - 161 of 161 matches
Mail list logo
