SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
               1      23861

SQL> alter table t1 compress;

Table altered.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
               1      23861

Finally:

SQL> alter table t1 move;
Table altered.
SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,

count(*)

from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);
COMPRESSION_TYPE   COUNT(*)

        4096      23861

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_COMPRESSION.html#GUID-8C9942CA-4EBD-48FF-9E8C-A59BF21A0176__BEIBFCCB

1 - No compression
...
4096 - Basic table compression

I have a database with a bunch of insert triggers (5) that fire pg_notify events.

These have all been working happily for about a year until 12:00 today where one of them seems to have come to a grinding hault.

I'm struggling to debug the issue.

The notification is picked up by a queue in rabbitMQ and my clients listen to that. As I said its been working fine for ages now, but went live last week and of course broke today.

I can't see the data hitting rabbitMQ, but at this stage can't work out if its a problem with the trigger not publishing the notification, or a problem with rabbit.

I can't find anything that tells me if the trigger has fired.

The code for the trigger is this

create or replace function public.notify_predl()
returns trigger
language plpgsql
as $function$
declare
my_record record;
begin
select 
            to_char(NEW.datetime, 'YYYY-MM-DD HH24:MI:SS') as datetime,
            NEW.duid, 
            NEW.agcstatus,  
            NEW.availability, 
            NEW.connectionpointid,
            NEW.dispatchmode,
            NEW.initialmw,
            NEW.intervention,
            to_char(NEW.lastchanged, 'YYYY-MM-DD HH24:MI:SS') as lastchanged,
            NEW.lower5min,
            NEW.lower5minactualavailability,
            NEW.lower5minflags, 
            NEW.lower60sec,
            NEW.lower60secactualavailability,
            NEW.lower60secflags,
            NEW.lower6sec, 
            NEW.lower6secactualavailability,
            NEW.lower6secflags,
            NEW.lowerreg, 
            NEW.lowerregactualavailability,
            NEW.lowerregflags,
            NEW.periodid,
            NEW.predispatchseqno,
            NEW.raise5min,
            NEW.raise5minactualavailability,
            NEW.raise5minflags, 
            NEW.raise60sec,
            NEW.raise60secactualavailability,
            NEW.raise60secflags,
            NEW.raise6sec, 
            NEW.raise6secactualavailability,
            NEW.raise6secflags,
            NEW.raisereg, 
            NEW.raiseregactualavailability,
            NEW.raiseregflags,
            NEW.rampdownrate, 
            NEW.rampuprate,
            NEW.runno,
            NEW.totalcleared, 
            NEW.semidispatchcap
            into my_record;
            perform pg_notify('proddl',row_to_json(my_record)::text);
            return NEW;
        end;
    $function$
    ;
===== trigger

drop trigger if exists notify_predl

on pdl;

create trigger notify_pdl

after insert on pdl

for each row

when (new.duid = 'BLAH')

execute procedure notify_pdl();

This really has me tossed at the moment.

I forgot to ask a question. How do you debug this. For more information I have now restarted the entire client setup, have restarted the database, reinstalled the trigger function and the trigger and still this particular one is not running.

select * from pg_notification_query_usage() 

returns 0.

listening for the event in psql shows that it is not infact firing at all (I listened to one that is working as well, and did get that notification).

Data is definitely hitting this table with the duid set correctly. How do I work out why this is not firing now?

I changed the trigger to after insert or update and still nothing fires.

Nope, my mistake. I didn't see the notifications in my session for some reason, but they did make it into the queue, so it seems to be working now. No idea why it needs the 'on update' now when its been working fine for months, but it seems to be working.

• If you are using Engine=MyISAM, you should convert to InnoDB.
• With InnoDB, you probably don't need LOCK TABLES.
• Why not switch to TEXT (in this case)?
• For long-running ALTERs, consider using Percona's pt-online-schema-change.
• Increasing a VARCHAR is faster in new versions of MySQL (including, at least, 8.0) It is faster because it is a "meta" change -- that is, the schema changes, but not the data.
• Decreasing a VARCHAR must go through all rows and truncate any rows with too much text.
• As a general rule, if the data has to change, ALTER must copy the table over.
• Altering the PRIMARY KEY requires restructuring the data's BTree, hence requiring a copy.
• Many changes to secondary indexes can be done in the background, so they appear to be instantaneous.

After a Centos server restart, unable to restart postgres??

I am getting a Postgres error: Unit postgresql.service not found I have tried every solution on Google, nothing works?? I would appreciate any pointers.

Thanks Clive

$ sudo find / -name initdb /local/bin/initdb /home/postgresql-12.2/src/bin/initdb /home/postgresql-12.2/src/bin/initdb/initdb

When I try sudo systemctl start, get an error.

sudo systemctl start /homepostgresql-12.2

Failed to start home-postgresql\x2d12.2.mount: Unit home-postgresql\x2d12.2.mount not found.

sudo systemctl start /home/postgresql-12.2.service

Failed to start home-postgresql\x2d12.2.service.mount: Unit home-postgresql\x2d12.2.service.mount not found.

sudo systemctl enable postgresql-12.2 Failed to enable unit: Unit file postgresql-12.2.service does not exist.

Your HAVING clause was the issue in your post's example query. HAVING is usually used to filter with aggregates within a group of values. Also your were evaluating if the max value was a date in the order of events in that HAVING clause, rather than filtering out non-date values first, then getting the max, such as your answer now does.

Another version of the query to get you the same answer would just be:

create table #tmp (
 dt varchar(20)
)
insert into #tmp values ('2022-02-15'),('2034-34-34'),('2022-03-14')

select max(convert(date, dt)) from #tmp

where isdate(dt)=1

This could potentially be faster by eliminating the use of an ORDER BY clause, especially if you indexed your table by the field dt like so CREATE CLUSTERED INDEX IX_TMP_DT ON #tmp (dt). This would store the data already sorted by the dt column then, which is beneficial if you were to have repeated runs of the SELECT query. One could verify the differences by looking at the execution plans, and time & I/O statistics.

Having Primary Keys or Unique constraints are great options but you would need additional error handling for this and the cost of overhead might not be worth it for your needs.

Another option would be an insert like this:

IF NOT EXISTS (SELECT 1 FROM Words WHERE Word = @newWord) BEGIN INSERT INTO Words (Word) VALUES (@newWord) END

That should do what you're hoping for and remain lightweight. Other option would be an on INSERT trigger to check same thing if you don't want to handle in your app logic and want it on DB side.

Approach 1: A fulltext index can't do everything a LIKE condition can do. The most common problem I see preventing use of fulltext is that MySQL's implementation only indexes whole words, so if your search is for a partial word it won't help.

It will take time to create the index, but if it's important to avoid downtime, you should definitely get acquainted with using https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html or https://github.com/github/gh-ost . These are both free tools that allow schema changes (including adding an index) without blocking clients from using the table in the meantime. We used pt-osc at my last job to run hundreds of schema changes per week during peak hours.

Approach 2: Adding a JSON column alone would not help, without also creating an index for the expression you want to search for. Have you used JSON in MySQL before? You should do a proof of concept with the version of MySQL you're using to see if it works, before you decide on this. There are a lot of limitations and caveats with using JSON in MySQL. I usually recommend not to use JSON.

Further reading:

• https://dev.mysql.com/doc/refman/8.0/en/json.html
• https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
• https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index
• https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

Approach 3: This is also called an inverted index. It can work well, but as you said it requires some work to maintain the inverted index, adding values as you add data. A comment above suggests using a trigger, which does work but think that also counts as adding more code.

Which of those approaches would solve the situation best?

I'd prefer the fulltext index if it meets the needs of the searches you need to support. The advantage is that it takes the least code to maintain the index, and that's a pretty big advantage.

But every time I hear the question "which is best?" I know that it's the wrong question. There is no "best" solution for all circumstances. Your project has its own requirements, and you need to choose the solution that fits those requirements. Any solution might be best for one person's application but it won't work for another application.

I'm trying to find equivalent of XMLQuery in PostgreSQL.

Following results I got from Oracle's https://livesql.oracle.com/ . Please suggest how to get same results on PostgreSQL.

CREATE TABLE person_data (
person_id   NUMBER(3),
person_data XMLTYPE);
INSERT INTO person_data

(person_id, person_data)

VALUES

(1, XMLTYPE('

Daniel Morgan

12/1/1951

damorgan@u.washington.edu

')

);
INSERT INTO person_data

(person_id, person_data)

VALUES

(2, XMLTYPE('

Taner Poder

5/17/1949

damorgan@u.washington.edu

')

);
SELECT person_id,

XMLQuery('for $i in /PDRecord

return $i/PDName'

PASSING BY VALUE person_data

RETURNING CONTENT) XMLData

FROM person_data;

Output:

 PERSON_ID  XMLDATA
1      Daniel Morgan

2      Taner Poder

I have a query where i need to select product options from multi tables.

SELECT 
p.option_id, 
p.product_id, 
p.sku, 
p.qty, 
p.price,
c.color_label,

s.size_label,

m.material_label,

v.variation_label
FROM product_options p
LEFT JOIN option_color c ON c.color_id = p.color_id

LEFT JOIN option_size s ON s.size_id = p.size_id

LEFT JOIN option_material m ON m.material_id = p.material_id

LEFT JOIN option_variation v ON v.variation_id = p.variation_id
WHERE p.product_id = 1 ;

This works fine. However; i get NULL values Like so

Business rule: as follows if any of the columns contains NULL means the whole product variation (column) of that particular option will be NULL.

MY QUESTION

How i can dynamically remove any of the columns once there result contain NULL.

Example from the above pic.

material_label = NULL

then the whole column should be removed.

I hope i explained it well.

EDIT

Just adding further explaanation.

What im trying to achive is as follow

1. Get specific product from the DB with its variants(try and minimize non required data).
2. List item then do some array cleaning and grouping then maybe convert it to JSON using PHP.
3. then using javascript to get user selection on the product.

https://shopee.com.my/%F0%9F%8C%B8READY-STOCK%F0%9F%8C%B8Korean-Striped-shirt-Woman%27s-Fashion-Casual-Loose-Tops-Long-sleeve-T-shirt-clothes-i.322596772.4157167158?sp_atk=c77b154e-7cf7-4c5a-a16b-e2ce3febe9dc&xptdk=c77b154e-7cf7-4c5a-a16b-e2ce3febe9dc

The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files.

Link: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#physical-properties-of-tempdb-in-sql-server

In server properties, processors section, I can see there are 2 NUMA nodes with 12 processors each. In the SQL error logs I can see: SQL Server detected 24 sockets with 1 cores per socket and 1 logical processors per socket. 24 total logical processors.

Automatic soft-NUMA was enabled because SQL server has detected hardware NUMA nodes 
with greater than 8 physical cores. 

So it looks like the computer has got 4 physical processor nodes, with 6 logical processors in each node. In this scenario what is the recommended value of number of tempdb and does it depend on the number of NUMA nodes or number of total logical processors or number of logical processors per NUMA node?

I am asking because the previous DBA has set the number of tempdb files to 6 (this is the number of logical processors in a single NUMA node). Whereas I understand based on the above MSDN link that the best practice is to configure the number of tempdb files as 8 or more in increments of 4 (12, 16, 20, 24).